Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
@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))))