i have three solutions to this (starting with a column containing Records):
custom function to expand the record column and from the new table, expand all underlying record columns and lists
- with dynamic rename of columns of new tables if the column-name already exists
custom function to expand only the record column
custom function to expand all records and all underyling records and lists RECURSIVELY
takes ages
with dynamic rename of columns of new tables if the column-name already exists -> FAILS SOMETIMES xD
[RECOMMENDATION]: Go for option 1. Why? -> Overall least effort and Power Query does it fastest.
look for the record columns in your table and paste them into the function like this by nesting muiltiple functions:
=expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(#"Previous step","components"),"hotel")
ENTER
in the new table, search for more record columns and nest them with functions around your original query:
=expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(expandRecordColAndFirstLevel(#"Expanded components1","components"),"hotel"),"payment_term"),"location"),"stages"),"address")
and so on...
In terms of query performance, it's better to nest the queries into 1 step even as well. This query took 45 seconds. Doing the expandRecordColAndFirstLevel(...) function in seperate steps takes 1,5x-2x of time.
here's the m-code for the custom functions (paste in the advanced editor of your function):
1 expandRecordColAndFirstLevel
(inputTable as table, columnName as text) as table =>
let
NonNullValues = List.Select(Table.Column(inputTable, columnName), each _ <> null and _ <> ""),
SampleValue = try NonNullValues{0} otherwise null,
ExpandedStep =
if SampleValue is record then
let
// Step 1: Get inner fields
ColumnsToExpand = try Table.ColumnNames(Table.FromRecords(NonNullValues)) otherwise {},
ExistingCols = Table.ColumnNames(inputTable),
RenamedCols = List.Transform(ColumnsToExpand, (c) =>
if List.Contains(ExistingCols, c) then c & "." & columnName else c
),
Step1 = Table.ExpandRecordColumn(inputTable, columnName, ColumnsToExpand, RenamedCols),
// Step 2: Check for list fields among newly added columns
NewListColumns = List.Select(RenamedCols, (col) =>
let sample = try Table.Column(Step1, col){0} otherwise null
in sample is list
),
// Step 3: Expand those list fields
Final = List.Accumulate(NewListColumns, Step1, (state, col) =>
Table.ExpandListColumn(state, col)
)
in
Final
else if SampleValue is list then
Table.ExpandListColumn(inputTable, columnName)
else
inputTable
in
ExpandedStep
2 expandRecordCol
(inputTable as table, columnName as text) as table =>
let
NonNullRecords = List.Select(
Table.Column(inputTable, columnName),
each _ <> null and _ <> ""
),
ColumnsToExpand = Table.ColumnNames(Table.FromRecords(NonNullRecords)),
ExistingColumns = Table.ColumnNames(inputTable),
RenamingMap = List.Transform(
ColumnsToExpand,
(col) =>
if List.Contains(ExistingColumns, col)
then {col, col & "." & columnName}
else {col, col}
),
Expanded = Table.ExpandRecordColumn(inputTable, columnName, ColumnsToExpand, List.Transform(RenamingMap, each _{1}))
in
Expanded
3 expandRecordColRecursive
let
RecursiveExpandColumn = (inputTable as table, columnName as text, optional path as text) as table =>
let
// Defaults
currentPath = if path = null then columnName else path & "." & columnName,
// Record Expansion with Safe Naming
ExpandRecordColumn = (tbl as table, colName as text, fullPath as text) as table =>
let
nonNulls = List.Select(Table.Column(tbl, colName), each _ <> null and _ <> ""),
colsToExpand = try Table.ColumnNames(Table.FromRecords(nonNulls)) otherwise {},
existingCols = Table.ColumnNames(tbl),
newNames = List.Transform(colsToExpand, (c) =>
if List.Contains(existingCols, c) or List.Contains(existingCols, fullPath & "." & c) then
c & "." & fullPath
else
c
),
expanded = Table.ExpandRecordColumn(tbl, colName, colsToExpand, newNames)
in
expanded,
// List Expansion
ExpandListColumn = (tbl as table, colName as text) as table =>
Table.ExpandListColumn(tbl, colName),
// Step 1: Expand the root column based on its type
sample = try Table.Column(inputTable, columnName){0} otherwise null,
firstExpanded =
if sample is record then ExpandRecordColumn(inputTable, columnName, currentPath)
else if sample is list then ExpandListColumn(inputTable, columnName)
else inputTable,
// Step 2: Recursively process any new complex columns
FullyExpand = (tbl as table) as table =>
let
// Get columns that are still records/lists
complexCols = List.Select(Table.ColumnNames(tbl), (col) =>
let v = try Table.Column(tbl, col){0} otherwise null
in v is record or v is list
),
// Skip if no complex columns left
nextStep =
if List.IsEmpty(complexCols) then
tbl
else
List.Accumulate(complexCols, tbl, (state, col) =>
let v = try Table.Column(state, col){0} otherwise null in
if v is record then
@RecursiveExpandColumn(state, col, col)
else if v is list then
@RecursiveExpandColumn(state, col, col)
else
state
)
in
nextStep
in
FullyExpand(firstExpanded)
in
RecursiveExpandColumn
