Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
@ImkeF @AlexisOlson @Anonymous @CNENFRNL
I have a table like following
Source = #table({"row", "__name", "id","location","salary","age","stack"}, {
{1, #table({"fName","lName"},{{"John", "Doe"}}),101, #table({"country","state"},{{"CA","ON"}}),100000,50,#table({"frontEnd","backEnd"},{{null,"SQL"}})}
,{2, #table({"fName","lName"},{{"Jane", "Doe"}}),102, #table({"country","state"},{{"CA","AB"}}),200000,35,#table({"frontEnd","backEnd"},{{"HTML","SQL"}})}
})
How can I ask PQ to dynamically and efficiently expand all the columns that have a table in them?
I want to end up with this
The full query is below
let
Source = #table({"row", "__name", "id","location","salary","age","stack"}, {
{1, #table({"fName","lName"},{{"John", "Doe"}}),101, #table({"country","state"},{{"CA","ON"}}),100000,50,#table({"frontEnd","backEnd"},{{null,"SQL"}})}
,{2, #table({"fName","lName"},{{"Jane", "Doe"}}),102, #table({"country","state"},{{"CA","AB"}}),200000,35,#table({"frontEnd","backEnd"},{{"HTML","SQL"}})}
}),
#"Expanded __name" = Table.ExpandTableColumn(Source, "__name", {"fName", "lName"}, {"fName", "lName"}),
#"Expanded location" = Table.ExpandTableColumn(#"Expanded __name", "location", {"country", "state"}, {"country", "state"}),
#"Expanded stack" = Table.ExpandTableColumn(#"Expanded location", "stack", {"frontEnd", "backEnd"}, {"frontEnd", "backEnd"})
in
#"Expanded stack"
Thank you in advance
Solved! Go to Solution.
= Table.FromRecords(Table.TransformRows(Source,each Record.Combine(List.TransformMany(Table.ToRows(Record.ToTable(_)),each if _{1} is table then Table.ToRecords(_{1}) else {Record.AddField([],_{0},_{1})},(x,y)=>y))))
@wdx223_Daniel's solutions is amazing but I also wanted to give this a shot without using any looping or recursion. I don't really know how efficient my solution is compared to other possibilities. Maybe @ImkeF has some intuition.
If your tables always have a single row, then you can take the first row as a record and then expand each row of your table like this:
let
Source = #table(
{"row", "__name", "id", "location", "salary", "age", "stack"},
{
{1, #table({"fName", "lName"}, {{"John", "Doe"}}), 101, #table({"country", "state"}, {{"CA", "ON"}}), 100000, 50, #table({"frontEnd", "backEnd"}, {{null, "SQL"}})},
{2, #table({"fName", "lName"}, {{"Jane", "Doe"}}), 102, #table({"country", "state"}, {{"CA", "AB"}}), 200000, 35, #table({"frontEnd", "backEnd"}, {{"HTML", "SQL"}})}
}
),
FirstRow = Table.First(Source),
AllCols = List.Union(
List.Transform(
Table.ColumnNames(Source),
each try Table.ColumnNames(Record.Field(FirstRow, _)) otherwise {_}
)
),
fn_expandRowToList = (row) =>
List.Union(
List.Transform(
Record.ToList(row),
each if _ is table then Record.ToList(Table.First(_)) else {_}
)
),
Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
Result
Here's a slightly cleaner equivalent with the source already in record (instead of table) form:
let
Source = #table(
{"row", "__name", "id", "location", "salary", "age", "stack"},
{
{1, [fName="John", lName="Doe"], 101, [country="CA", state="ON"], 100000, 50, [frontEnd=null, backEnd="SQL"]},
{2, [fName="Jane", lName="Doe"], 102, [country="CA", state="AB"], 200000, 35, [frontEnd="HTML", backEnd="SQL"]}
}
),
FirstRow = Table.First(Source),
AllCols = List.Union(
List.Transform(
Table.ColumnNames(Source),
each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
)
),
fn_expandRowToList = (row) =>
List.Union(
List.Transform(
Record.ToList(row),
each if _ is record then Record.ToList(_) else {_}
)
),
Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
Result
The logic for expanding the column names and column values is quite similar. Here's how AllCals is calculated:
AllCols
= List.Union(
List.Transform(
Table.ColumnNames(Source),
each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
)
)
= List.Union(
List.Transform(
{"row", "__name", "id", "location", "salary", "age", "stack"},
each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
)
)
= List.Union(
{
{"row"},
Record.FieldNames(Record.Field(FirstRow, "__name")),
/* = Record.FieldNames(FirstRow[__name])*/
{"id"},
Record.FieldNames(Record.Field(FirstRow, "location")),
/* = Record.FieldNames(FirstRow[location])*/
{"salary"},
{"age"},
Record.FieldNames(Record.Field(FirstRow, "stack"))
/* = Record.FieldNames(FirstRow[stack])*/
}
)
= List.Union(
{
{"row"},
{"fName", "lName"}
{"id"},
{"country", "state"},
{"salary"},
{"age"},
{"frontEnd", "backEnd"}
}
)
= { "row", "fName", "lName", "id", "country", "state", "salary", "age", "frontEnd", "backEnd" }
The function fn_expandRowToList does nearly the same except that it reads the field values instead of the field names.
fn_expandRowToList(FirstRow)
= List.Union(
List.Transform(
Record.ToList(row),
each if _ is record then Record.ToList(_) else {_}
)
)
= List.Union(
List.Transform(
Record.ToList(
[
row = 1,
__name = [fName="John", lName="Doe"],
id = 102,
location = [country="CA", state="ON"],
salary = 100000,
age = 50,
stack = [frontEnd=null, backEnd="SQL"]
]
),
each if _ is record then Record.ToList(_) else {_}
)
)
= List.Union(
List.Transform(
{1, [fName="John", lName="Doe"], 102, [country="CA", state="ON"], 100000, 50, [frontEnd=null, backEnd="SQL"]},
each if _ is record then Record.ToList(_) else {_}
)
)
= List.Union(
{
{1},
Record.ToList([fName="John", lName="Doe"]),
{101},
Record.ToList([country="CA", state="ON"]),
{100000},
{50},
Record.ToList([frontEnd=null, backEnd="SQL"]),
}
)
= {1, "John", "Doe", 101, "CA", "ON", 100000, 50, null, "SQL"}
Hi @AlexisOlson ,
nice solutions!
But I don't see any recursion in @wdx223_Daniel s solution.
As he seems to call the source data less times, I would imagine his solution being faster.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
I didn't mean to suggest Daniel's solution involves looping or recursion. It does not and I wanted to see if I could do something similar (as opposed to Chris Webb's solution I linked to, which is recursive).
@wdx223_Daniel's solutions is amazing but I also wanted to give this a shot without using any looping or recursion. I don't really know how efficient my solution is compared to other possibilities. Maybe @ImkeF has some intuition.
If your tables always have a single row, then you can take the first row as a record and then expand each row of your table like this:
let
Source = #table(
{"row", "__name", "id", "location", "salary", "age", "stack"},
{
{1, #table({"fName", "lName"}, {{"John", "Doe"}}), 101, #table({"country", "state"}, {{"CA", "ON"}}), 100000, 50, #table({"frontEnd", "backEnd"}, {{null, "SQL"}})},
{2, #table({"fName", "lName"}, {{"Jane", "Doe"}}), 102, #table({"country", "state"}, {{"CA", "AB"}}), 200000, 35, #table({"frontEnd", "backEnd"}, {{"HTML", "SQL"}})}
}
),
FirstRow = Table.First(Source),
AllCols = List.Union(
List.Transform(
Table.ColumnNames(Source),
each try Table.ColumnNames(Record.Field(FirstRow, _)) otherwise {_}
)
),
fn_expandRowToList = (row) =>
List.Union(
List.Transform(
Record.ToList(row),
each if _ is table then Record.ToList(Table.First(_)) else {_}
)
),
Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
Result
Here's a slightly cleaner equivalent with the source already in record (instead of table) form:
let
Source = #table(
{"row", "__name", "id", "location", "salary", "age", "stack"},
{
{1, [fName="John", lName="Doe"], 101, [country="CA", state="ON"], 100000, 50, [frontEnd=null, backEnd="SQL"]},
{2, [fName="Jane", lName="Doe"], 102, [country="CA", state="AB"], 200000, 35, [frontEnd="HTML", backEnd="SQL"]}
}
),
FirstRow = Table.First(Source),
AllCols = List.Union(
List.Transform(
Table.ColumnNames(Source),
each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
)
),
fn_expandRowToList = (row) =>
List.Union(
List.Transform(
Record.ToList(row),
each if _ is record then Record.ToList(_) else {_}
)
),
Result = Table.FromRows(Table.TransformRows(Source, fn_expandRowToList), AllCols)
in
Result
The logic for expanding the column names and column values is quite similar. Here's how AllCals is calculated:
AllCols
= List.Union(
List.Transform(
Table.ColumnNames(Source),
each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
)
)
= List.Union(
List.Transform(
{"row", "__name", "id", "location", "salary", "age", "stack"},
each try Record.FieldNames(Record.Field(FirstRow, _)) otherwise {_}
)
)
= List.Union(
{
{"row"},
Record.FieldNames(Record.Field(FirstRow, "__name")),
/* = Record.FieldNames(FirstRow[__name])*/
{"id"},
Record.FieldNames(Record.Field(FirstRow, "location")),
/* = Record.FieldNames(FirstRow[location])*/
{"salary"},
{"age"},
Record.FieldNames(Record.Field(FirstRow, "stack"))
/* = Record.FieldNames(FirstRow[stack])*/
}
)
= List.Union(
{
{"row"},
{"fName", "lName"}
{"id"},
{"country", "state"},
{"salary"},
{"age"},
{"frontEnd", "backEnd"}
}
)
= { "row", "fName", "lName", "id", "country", "state", "salary", "age", "frontEnd", "backEnd" }
The function fn_expandRowToList does nearly the same except that it reads the field values instead of the field names.
fn_expandRowToList(FirstRow)
= List.Union(
List.Transform(
Record.ToList(row),
each if _ is record then Record.ToList(_) else {_}
)
)
= List.Union(
List.Transform(
Record.ToList(
[
row = 1,
__name = [fName="John", lName="Doe"],
id = 102,
location = [country="CA", state="ON"],
salary = 100000,
age = 50,
stack = [frontEnd=null, backEnd="SQL"]
]
),
each if _ is record then Record.ToList(_) else {_}
)
)
= List.Union(
List.Transform(
{1, [fName="John", lName="Doe"], 102, [country="CA", state="ON"], 100000, 50, [frontEnd=null, backEnd="SQL"]},
each if _ is record then Record.ToList(_) else {_}
)
)
= List.Union(
{
{1},
Record.ToList([fName="John", lName="Doe"]),
{101},
Record.ToList([country="CA", state="ON"]),
{100000},
{50},
Record.ToList([frontEnd=null, backEnd="SQL"]),
}
)
= {1, "John", "Doe", 101, "CA", "ON", 100000, 50, null, "SQL"}
= Table.FromRecords(Table.TransformRows(Source,each Record.Combine(List.TransformMany(Table.ToRows(Record.ToTable(_)),each if _{1} is table then Table.ToRecords(_{1}) else {Record.AddField([],_{0},_{1})},(x,y)=>y))))