When I’m working with XML files, or web pages, or any data with columns containing nested tables in Power Query, I often end up having to expand every expandable column in the table and then expanding any new columns that are revealed after that to find the data that I’m looking for. This is a such a pain I thought I’d write a function to do it for me – which is the subject of this post.
For example, consider the following XML:
If you load this into Power Query you will see the following table created for the first step:
To get to a table where all of the data is visible requires clicking on the expand icons in the address and the employees columns (highlighted), and then three more clicks after that. Sigh.
Here’s my function, called ExpandAll, to expand all the columns in a table that can be expanded:
let
//Define function taking two parameters - a table and an optional column number
Source = (TableToExpand as table, optional ColumnNumber as number) =>
let
//If the column number is missing, make it 0
ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
//Find the column name relating to the column number
ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
//Get a list containing all of the values in the column
ColumnContents = Table.Column(TableToExpand, ColumnName),
//Iterate over each value in the column and then
//If the value is of type table get a list of all of the columns in the table
//Then get a distinct list of all of these column names
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents,
each if _ is table then Table.ColumnNames(_) else {}))),
//Append the original column name to the front of each of these column names
NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & "." & _),
//Is there anything to expand in this column?
CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
//If this column can be expanded, then expand it
ExpandedTable = if CanExpandCurrentColumn
then
Table.ExpandTableColumn(TableToExpand, ColumnName,
ColumnsToExpand, NewColumnNames)
else
TableToExpand,
//If the column has been expanded then keep the column number the same, otherwise add one to it
NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
//If the column number is now greater than the number of columns in the table
//Then return the table as it is
//Else call the ExpandAll function recursively with the expanded table
OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1)
then
ExpandedTable
else
ExpandAll(ExpandedTable, NextColumnNumber)
in
OutputTable
in
Source
You can then use this function on the XML file shown above as follows:
let
//Load XML file
Source = Xml.Tables(File.Contents("C:\Users\Chris\Documents\PQ XML Expand All Demo.xml")),
ChangedType = Table.TransformColumnTypes(Source,{{"companyname", type text}}),
//Call the ExpandAll function to expand all columns
Output = ExpandAll(ChangedType)
in
Output
And bingo, in one step, you get everything:
You can download the sample workbook here.
Very usefull script!! thnx for sharing! saves a lot of clicks
Having read Curt’s reply to your question regarding recursion vs. List.Generate() loops — even more relevant for my dynamically loaded functions due to loading overhead within every iteration — I decided to try and see if I could rewrite all functions I had in order to avoid recursion.
That turned out more of a challenge for this function. I plugged most of the original code into List.Generate()’s ‘next’ block, at which point Power Query started complaining each of the var names used in the block would now need to be initialized in its ‘start’ block as well (not to mention adding square brackets everywhere).
I eventually figured out a workaround by embedding a let-expression into the next block, allowing the flexibility of declaring regular variables without requiring the initialization and square brackets of the iteration Record.
Resulting code here. I hadn’t left the original comments intact, nor tested for performance gains, but it feels like I learned a lot. I also added a parameter to optionally leave out the appended parent column names whenever possible.
Nice work!
Hi Chris,
Sample work book is not working, can you please attach.
Thanks
Hello Chris,
Thanks for your knowledge sharing. I have been having some trouble with the nested function. it seems that PowerQuery does not recognize it when I am trying to input it. I get an error : “Expression error : ExpandAll is not recognized ,Check your spelling (I’m translating the error from Hebrew so i hope i got it right….).do you know this kind of error ?
thanks
Hi Jacky,
The error is implying it doesn’t currently have a query named ExpandAll.
If you’ve imported the query, did you make sure to name it that?
it seems odd. when creating the function with the same syntax and name, the error appears. only when i remove the line that inner call the same function (“OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1) then ExpandedTable else ExpandAll(ExpandedTable, NextColumnNumber)”) it seems ok.
when I am using the same function in the sample xls file everything seems OK.
Okay, I think I get it now… could you try adding a ‘@’ to the recursive call, like this: @ExpandAll?
Any guidance on how to apply this approach to a SQL query result that includes an XML column? I could flatten it out using XML functionality in SQL, but thought it would be simple to apply this approach in Power Query. The challenge is that the XML column is retrieved as a text column, and is not treated as an expandable table.
Forget it. I Just found the “Parse” option on the Transform tab. How did I miss that? JSON too!
This works well, thanks Chris! It’s a bit of a performance killer though – it’s adding about 2 minutes to a 3 second query. I’m surprised there’s not an “expand all” option in PQ.
Hi Chris, Thanks it works very nice.
Now I’ve got a next question. I Work in a big Univerity Hospital and we get a lot of binary XML files as well as JSON files from Oracle and PostgreSQL databases.
Because the JSON files have the record-Type and the XML Files have the table-Type the ExpandAll function doesn’t work with the record-Type.
I’m searching for a Funktion for several days now but cannot find anything and am not familiar enough with M-Code. Do you have a Sugestion?
Sorry no – it will be possible, I’m sure, but it would take me some time to work out how to do it.
@Rob I think I’ve got this working on record-Type. I duplicated your function to “ExpandAllRecords” and made 3 edits:
1. replaced “each if _ is table then Table.ColumnNames(_)” with “each if _ is record then Record.FieldNames(_)”
2. replaced “Table.ExpandTableColumn” with “Table.ExpandRecordColumn”
3. replaced “ExpandAll” with “ExpandAllRecords”
I tried getting both tables and records expanding in one function, but I kept getting type errors.
@Mike I was wondering if you manage to fix the issue. I am looking at one of the xml data that have both value and table in the column that fails when used the with ExpanAll Function.
Hi Mike! Im tryng to use your function and its returning an error. Can you help me?
@Thakks no I didnt. Perhaps you could call ExpandAllRecords, then ExpandAll ?
Fantastic. After an hour of trying to create the same thing I was ready to throw my laptop at the wall – so the fact that you put a template out into the world that does this is very much appreciated. Seriously. Huge amounts of thanks.
Thanks for sharing. I think this is a workaround for an issue/bug in PQ, so I created an item in the PBI issue list. Pleas consider to upvote! Thx.
https://community.powerbi.com/t5/Issues/Power-Query-should-not-wrap-simple-xml-elements-in-a-table/idi-p/125525#M3038
I am getting a list of records after making bunch of API calls.
Table:
Items
Table
Table
Error
Table
Tables expand fine and show their contents.
Errors give me:
—
An error occurred in the ‘ExpandAll’ query.
Expression.Error: The column ‘Column1’ of the table wasn’t found.
Details:
Column1
—
Not sure how to handle this.
Chris, thanks for this…a useful little work-around. Hopefully the Microsoft Gods will realise that this is something that should just be native…
In the meantime though…how would I tweak this such that the “Use Original Column Name as Prefix” setting is deselected so that I only get the actual column names and not “Custom.xxxx”
Thoughts?
Hi Denver, you can’t – the column names are already hard-coded in the M, so you have to edit the M the UI generates.
Hey folks,
As far as I understand the function it should be able to handle columns with a Mix of Tables and value, right? However as soon as a column contains some Tables and some Dates for Instance, I keep getting an error saying that the value (e.g. 01.01.2020) could not be transformed into a Table…any idea?
Thanks in advanced!
In my XML query I was occasionally getting whole columns which would return as Tables. Deploying this initial function took forever because it had to iterate over entire columns which I knew would be text. Since I knew the whole column would be a table I was able to get a dramatic performance increase by only checking if the first element in the column is a table. Check out the code below and I hope its useful to someone!
let
//Define function taking two parameters – a table and an optional column number
Source = (TableToExpand as table, optional ColumnNumber as number) =>
let
//If the column number is missing, make it 0
ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
//Find the column name relating to the column number
ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
//Get a list containing all of the values in the column
ColumnContents = Table.Column(TableToExpand, ColumnName),
CanExpandCurrentColumn = if List.First(ColumnContents) is table then true else false,
//If this column can be expanded, then expand it
ExpandedTable = if CanExpandCurrentColumn
then
Table.ExpandTableColumn(TableToExpand, ColumnName,
{ColumnName})
else
TableToExpand,
//If the column has been expanded then keep the column number the same, otherwise add one to it
NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
//If the column number is now greater than the number of columns in the table
//Then return the table as it is
//Else call the ExpandAll function recursively with the expanded table
OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1)
then
ExpandedTable
else
ExpandAll(ExpandedTable, NextColumnNumber)
in
OutputTable
in
Source
Thanks!
Sorry, should amend to
let
//Define function taking two parameters – a table and an optional column number
Source = (TableToExpand as table, optional ColumnNumber as number) =>
let
//If the column number is missing, make it 0
ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
//Find the column name relating to the column number
ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
//Get a list containing all of the values in the column
ColumnContents = Table.Column(TableToExpand, ColumnName),
CanExpandCurrentColumn = if List.First(ColumnContents) is table then true else false,
//If this column can be expanded, then expand it
ExpandedTable = if CanExpandCurrentColumn
then
Table.ExpandTableColumn(TableToExpand, ColumnName,
{“Element:Text”}, {ColumnName})
else
TableToExpand,
//If the column has been expanded then keep the column number the same, otherwise add one to it
NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
//If the column number is now greater than the number of columns in the table
//Then return the table as it is
//Else call the ExpandAll function recursively with the expanded table
OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1)
then
ExpandedTable
else
ExpandAll(ExpandedTable, NextColumnNumber)
in
OutputTable
in
Source
Brillant – thanks!!
This works for me, and stops me trying in vain to figure out how to do this!
Hi Chris,
This code has been a life saver for me, I am a beginner and have been trying to find a work around for my project for ages . Thanks for this amazing solution. I have adapted Owens version ( Thanks Owen !) to speed up the processing time. It worked amazingly and now allows me to change the source dynamically. The last hurdle I am having is if there is not data returned it returns [expression.error] The column “Row” of the table was not found. It references the Row in the Query that the invoked function points to. #”Expanded Row” = Table.ExpandTableColumn(#”Changed Type”, “Row”, {“Portfolio”, “CaseId” and so on. I have absolutely no clue how to fix this , could you suggest a work around or point me to a good article to help me try fix this? Thanks in advance
I guess using a try otherwise statement might help here (see https://blog.gbrueckl.at/2013/12/error-handling-in-power-query/), so something like
#”Expanded Row” = try Table.ExpandTableColumn(#”Changed Type”, “Row”, {“Portfolio”, “CaseId” …. otherwise #”Changed Type”
This worked ! Thanks again !
Hi Michelle, can you post your final code from this? I’m in the exact same situation and would be super helpful.
Hi Chris,
Is there any guidance for JSON format dynamic extraction of all List and Records ?
Thanks,
You’ll be able to do it in M, but I don’t think anyone has blogged about it and it would take me a long time to write the code.
Chris, could you expand on this a bit? I am trying to use a JSON object that I pulled from an API (GraphQL), but it appears that the function is not able to expand a list in a column…
Here is more detail:
I am working on getting this to working using GraphQL API. I am running into an issue where the function is not expanding all the records.
I am creating a JSON object from the API, which is being returned to M as a record. I am converting that record to a table; in that step a table is created with two columns. The second column is called [value] and it contains one record that is a LIST. The function does not iterate over the list, and does not expand all of the columns.
I am not sure exactly why this is happening, but it appears that the function is running into the list as an object, and not just finding records in the table.
Any advice on how to alter the M so deal with that list in the [Value] column of my table created out of the original JSON record?
Thank you for posting this!
Hi Chris,
I would like to Expand every row in a table, but when i use:
#”Transform file from binary” = each #”Other removed columns” [Transform file from binary],
#”Expanded table” = ExpandAll(#”Transform file from binary””,null)
i get a cyclic reference warning ¿Any suggestion? Thanks in advance.
Hello Chris,
Really powerful function you built!
Thank you for the effort you did so far.
However, I still have a kind of problem when trying to adjust the code into my query.
It states: “Expression.Error: We cannot apply indexing to the type List”
Do you have any idea how I can fix this issue?
Thank you in advance!
Hi Chris,
Great function! I modified your function a bit to expand both columns of tables and records, to make it work for json files as well!
I post my solution here in hope that someone will find it useful!
The modification I made:
1. An extra else if statement in “ColumnsToExpand”
“else if _ is record then Record.FieldNames(_) in “ColumnsToExpand”
2. Added an extra variable “IsRecordColumn”
IsRecordColumn = Type.Is(Value.Type(ColumnContents{0}), type record),
3. Implemented an extra if statement in “ExpandedTable”
if IsRecordColumn then
Table.ExpandRecordColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames)
else
Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames)
let
// Expands all (potentially nested) table and record columns
Source = (TableToExpand as table, optional ColumnIndex as number) =>
let
ColIndex = if ColumnIndex is null then 0 else ColumnIndex,
ColumnName = Table.ColumnNames(TableToExpand){ColIndex},
ColumnContents = Table.Column(TableToExpand, ColumnName),
//Create unique list of column names by iterating all rows in column
ColumnsToExpand = List.Distinct(
List.Combine(
List.Transform(
ColumnContents,
each
if _ is table then
Table.ColumnNames(_)
else if _ is record then
Record.FieldNames(_)
else
{}
)
)
),
//Append the original column name to the front of each of these column names
NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & “.” & _),
//If the column can be expanded, then expand it
IsColumnExpandable = List.Count(ColumnsToExpand) > 0,
IsRecordColumn = Type.Is(Value.Type(ColumnContents{0}), type record),
ExpandedTable = if IsColumnExpandable then
if IsRecordColumn then
Table.ExpandRecordColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames)
else
Table.ExpandTableColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames)
else
TableToExpand,
//If the column has been expanded then keep the column number the same, otherwise add one to it
NextColumn = if IsColumnExpandable then ColIndex else ColIndex + 1,
// Recursively call function until column index is greater than number of columns
OutputTable = if NextColumn>(Table.ColumnCount(ExpandedTable)-1) then
ExpandedTable
else
ExpandAllColumns(ExpandedTable, NextColumn)
in
OutputTable
in
Source
Thanks!
Hi,
Thanks for this. I copied your script, but the function gives the error “‘ExpandAllColumns’ wasn’t recognized. Make sure it’s spelled correctly.”.
My columns have first List and then Records (after selecting “Extract to new rows”). I hope this will work.
Thanks.
You need to call the query you pasted the code into ExpandAllColumns I think
This is a pretty good time saver, thank you !!
Hi Chris.. I have nested lists or records. For example, I Will explain below.
Step 1: I imported JSON into Power BI. I could see more number of List
Step 2: I have converted that into table. Now I could see expand function on top right corner. I just clicked it got expanded.
Step 3: I got 4 columns. Out of which 3 columns have values 1 column has list
Step 4: I converted that into table and drill down that list.
Step 5: Now again I could see some 7 columns out of that 5 are values 2 are records.
Step 6: Again I expanded that inside I could see more number of list and records.
So for these kind of nested List/Record is that possible we have any M-Query. Could you please advice me whether the mentioned Power Query will work for nested records/Lists. Since I am new to Power Bi writing Query itself need more strugglging please guide me how to handle this.
Hi Chitra,
I modified Chris code for Records. Works for me. Code below. This was at the limit of my current capabilities 🙂 Sure it can be improved
let
//Define function taking two parameters – a table and an optional column number
Source = (TableToExpand as table, optional ColumnNumber as number) =>
let
//If the column number is missing, make it 0
ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
//Find the column name relating to the column number
ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
//Get a list containing all of the values in the column
ColumnContents = Table.Column(TableToExpand, ColumnName),
//Iterate over each value in the column and then If the value is of type record get a list of all of the columns in the record. Then get a distinct list of all of these column names
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents, each if _ is record then Record.ToTable(_)[Name] else {}))),
//Append the original column name to the front of each of these column names
NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & “.” & _),
//Is there anything to expand in this column?
CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
//If this column can be expanded, then expand it
ExpandedTable = if CanExpandCurrentColumn
then Table.ExpandRecordColumn(TableToExpand, ColumnName, ColumnsToExpand, NewColumnNames)
else TableToExpand,
//If the column has been expanded then keep the column number the same, otherwise add one to it
NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
////If the column number is now greater than the number of columns in the table – Then return the table as it is – Else call the ExpandAll function recursively with the expanded table
OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1)
then ExpandedTable
else ExpandAllRecords(ExpandedTable, NextColumnNumber)
in
OutputTable
in
Source
Thanks a lot, this worked great, I just had to make sure I didn’t apply it instantly as the XML I have to go through has quite a lot of fuzz in it, but after taking a few steps more structured this works like a charm.
Only return null values and not new added columns, suggest and commen please?
Hi Chris,
I just found this and its really good! It works like a charm in PowerBI Desktop.
I tried to use it on PowerQuery dataflows in PowerBI web and its not working. The moment I renamed the function to ExpandAll it says “A cyclic reference was encountered during evaluation.”
Do you know what could be different?
Thanks,
Mauel
Did you manage to sort this out I have the same problem
Hi Chris,
Thank you for sharing this. I get an error message saying that a value can’t be converted to type Table. Any ideas on how to fix this?
Thx Chris! You’ve saved my job! I’ve been looking it for a long time.
There is a much easier method for loading fully expanded XML files into PowerQuery. Open the xml file from within Excel. It will give you three options. Pick the first one – As an XML table. Then, simply Get and Transform Data from Table/Range and voila!
Hi,
I am not successful with implementing above code.
Nothing seems to change in the Table query (named Query1). The columns are still “compressed” as tables)
I have copied/paste above queries (just changed to my XML with the exact same demo as Chris)
Query1:
let
//Load XML file
Source = Xml.Tables(File.Contents(“C:\PQ XML Expand All Demo.xml”)),
ChangedType = Table.TransformColumnTypes(Source,{{“companyname”, type text}}),
//Call the ExpandAll function to expand all columns
Output = ExpandAll(ChangedType)
in
Output
fxExpandAll:
let
//Define function taking two parameters – a table and an optional column number
Source = (TableToExpand as table, optional ColumnNumber as number) =>
let
//If the column number is missing, make it 0
ActualColumnNumber = if (ColumnNumber=null) then 0 else ColumnNumber,
//Find the column name relating to the column number
ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},
//Get a list containing all of the values in the column
ColumnContents = Table.Column(TableToExpand, ColumnName),
//Iterate over each value in the column and then
//If the value is of type table get a list of all of the columns in the table
//Then get a distinct list of all of these column names
ColumnsToExpand = List.Distinct(List.Combine(List.Transform(ColumnContents,
each if _ is table then Table.ColumnNames(_) else {}))),
//Append the original column name to the front of each of these column names
NewColumnNames = List.Transform(ColumnsToExpand, each ColumnName & “.” & _),
//Is there anything to expand in this column?
CanExpandCurrentColumn = List.Count(ColumnsToExpand)>0,
//If this column can be expanded, then expand it
ExpandedTable = if CanExpandCurrentColumn
then
Table.ExpandTableColumn(TableToExpand, ColumnName,
ColumnsToExpand, NewColumnNames)
else
TableToExpand,
//If the column has been expanded then keep the column number the same, otherwise add one to it
NextColumnNumber = if CanExpandCurrentColumn then ActualColumnNumber else ActualColumnNumber+1,
//If the column number is now greater than the number of columns in the table
//Then return the table as it is
//Else call the ExpandAll function recursively with the expanded table
OutputTable = if NextColumnNumber>(Table.ColumnCount(ExpandedTable)-1)
then
ExpandedTable
else
ExpandAll(ExpandedTable, NextColumnNumber)
in
OutputTable
in
Source