Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
@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))))
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
17 | |
9 | |
8 | |
7 | |
7 |