When working with Power Query, you might sometimes need to expand Table or Record columns in order to get to the data you want. For example, a table named “A” may be comprised of three normal columns and a fourth column containing a record. This means that the fourth column is made up of MULTIPLE columns that you can drill into. The way you drill down to the next level is very intuitive within Power Query, but the column names will be hard-coded (FOREVER). If your data source changes or the available columns vary for each record, you may face problems as you will only expand certain columns because of the hard-coding.
In short, the solution is to expand the record or table dynamically by understanding all the possible child columns and then expanding the parent record/table with said child columns. The video above provides a more in-depth approach as to how I arrived at the following piece of code, but I will outline each step below:
Solution:
Table.ExpandRecordColumn(
MyTable,
“ColumnToExpand”,
Table.ColumnNames(
Table.FromRecords(
List.Select(
Table.Column(
MyTable,
“ColumnToExpand”,
) each _ <> “” and _ <> null
)
)
),
Table.ColumnNames(
Table.FromRecords(
List.Select(
Table.Column(
MyTable,
“ColumnToExpand”,
) each _ <> “” and _ <> null
)
)
),
)
Explanation
This code may look a bit daunting, but it is easy to understand if you break it down in pieces. I’ve divided the code into three colored sections, blue, green, and orange. We’ll start with the green section beginning from the inside and working out. The List.Select(Table.Column()) piece is simply turning our expandable column into a list. We then transform this list into a table with Table.FromRecords() and then we grab the column names with the Table.ColumnNames() function. This provides us with all of the possible columns in our “ColumnToExpand” column. The blue section is using the Table.ExpandRecordColumn() function to expand our “ColumnToExpand” column, but since we are using the green section as input, we are dynamically expanding all of the columns available to us. The orange section is a repeat of the green section and is used as the 4th parameter to the blue section, providing the names of the expandable columns.
And with that, we are now able to expand a table or record column dynamically based on the available column names in the child table/record. We no longer have hard-coded column names in our code!
9 Comments
Leave a ReplyCancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Can you do one for tables ?
Dear Parker, I hope you’re well.
I thought this was exactly the code I needed but when I look a bit closer your example is slightly different to my problem.
Your expandable column (ColumnToExpand) is a set of Record types. In my case, I have a column of Table types which is the result of a Table.NestedJoin command previously.
Is there similar code to the neat and compact example you give above that I can use on my column type i.e. Table? I have tried several combinations of code but can’t get it to work.
Many thanks, Matt.
Hello Matt, I am facing same challenge with table column data too. have you been able to resolve this yet?
Hi how about applying to transpose tables ? the codes are below
let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
#”Changed Type” = Table.TransformColumnTypes(Source,{{“DOA”, type date}}),
#”Grouped Rows” = Table.Group(#”Changed Type”, {“Sr.”}, {{“Grouped”, each _, type table [#”Sr.”=number, DOA=nullable date]}}),
#”Added Custom” = Table.AddColumn(#”Grouped Rows”, “Custom”, each Table.Transpose(Table.SelectColumns([Grouped], “DOA”))),
#”Expanded Custom” = Table.ExpandTableColumn(#”Added Custom”, “Custom”, {“Column1”, “Column2”, “Column3”}, {“Column1”, “Column2”, “Column3″}),
#”Removed Columns” = Table.RemoveColumns(#”Expanded Custom”,{“Grouped”}),
#”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“Column1”, “DOA Date1”}, {“Column2”, “DOA Date2”}, {“Column3”, “DOA Date3″}}),
#”Changed Type1″ = Table.TransformColumnNames(#”Renamed Columns”, each Text.Replace(_, “Column”, “DOA Date”)),
#”Changed Type2″ = Table.TransformColumnTypes(#”Changed Type1″,{{“DOA Date1”, type date}, {“DOA Date2”, type date}, {“DOA Date3″, type date}})
in
#”Changed Type2”
you example is fromRecord, is it possible to apply same technique for “Column1”, “Column2”, “Column3”, the column values will be increasing and each time need to come to expanded custom to select those newly added columns
than you !!
Thanks for the provided example, much appreciated.
In my case I was reading multiple json file where not all files had the same column names. The code you have only processes the names of the first record.
After some tinkering I came with the following variation on your code where the Record.Combine merges all rows into one set to get the column names of all records.
= Table.ExpandRecordColumn(MyTable,
“Activities”,
Table.ToList(Table.SelectColumns(Record.ToTable(Record.Combine(List.Select(Table.Column(MyTable,”Activities”), each _ “” and _ null))), “Name”)),
Table.ToList(Table.SelectColumns(Record.ToTable(Record.Combine(List.Select(Table.Column(MyTable,”Activities”), each _ “” and _ null))), “Name”))
)
There must be missing something after the comma in :
“ColumnToExpand”,)
Thanks for this! It was perfect for my scenario
This pointed me in the right direction, but there appears to be a lot of unnecessary steps here. To break it down starting from the inner most function and working out:
Table.Column returns a list.
That is fed into List.Select, which is not needed unless you really do have zero-len or null records that you want to exclude. Otherwise, you already have a list.
The list is fed into Table.FromRecords, which creates a table out of the list.
At this point, you should be done. You have accomplished creating a table that contains all of the data.
The table is then passed to Table.ColumnNames to extract the names of all its columns.
The list of column names is passed to Table.ExpandRecordColumn so as to extract the data again.
I believe the entire code reduces to:
Table.FromRecords(Table.Column(MyTable,“ColumnToExpand”))
unless you want to filter columns, in which case you would specify your criteria:
Table.FromRecords(List.Select(Table.Column(#”Expanded Column1″, “Object”), each ))
Be aware that the code doesn’t work as it is: you must move the two commas after “ColumnToExpand”, right before the corresponding “each”
Also, to use it for tables instead of records, you should change every Table.FromRecords to a Table.Combine and Table.ExpandRecordColumn to Table.ExpandTableColumn