ExpandAll function for Power Query or Power BI - expands all table-type columns recursively

ExpandAll.M

// From Chris Webb's blog - http://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/

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

Hi Mike, 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!

I should mention, I also used your other script that you mentioned was for JSON where you iterate over Records instead of Tables, but I had the same issue.

I would just use the UI to expand the List. That's a simple step that cant be recursive, so doesnt need to call a function.