Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
@ImkeF @AlexisOlson @watkinnc @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
Proud to be a Super User!
New Animated Dashboard: Sales Calendar
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))))
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!