Expanding All Columns In A Table In Power Query

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:


    //Define function taking two parameters - a table and an optional column number 

    Source = (TableToExpand as table, optional ColumnNumber as number) =>


     //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 


                         Table.ExpandTableColumn(TableToExpand, ColumnName, 

                                ColumnsToExpand, NewColumnNames) 



     //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) 




                        ExpandAll(ExpandedTable, NextColumnNumber)






You can then use this function on the XML file shown above as follows:


    //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)




And bingo, in one step, you get everything:


You can download the sample workbook here.

25 responses

  1. Pingback: Dew Drop – May 22, 2014 (#1782) | Morning Dew

  2. 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.

  3. Pingback: VoluntaryTracking.Enabled = true; | Faister, say what?

  4. Pingback: Analysing SSAS Extended Event Data With Power Query: Part 1 | Chris Webb's BI Blog

    • 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 ?


      • 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.

  5. Pingback: Multiple Replacements of words in Power Query – Ivan Bond's blog

  6. 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.

  7. 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.

  8. 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?

  9. @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.

  10. 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.

  11. I am getting a list of records after making bunch of API calls.


    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.

    Not sure how to handle this.

  12. 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”


  13. Pingback: Making Sure All Columns Appear When You Combine Data From Multiple Files In Power BI/Power Query M – Chris Webb's BI Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: