Expand all nested tables from loaded XML file

Question

I am using the function ExpandAll to expand all tables recursively from a loaded XML file.

Function is:

let
    // http://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
    //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

When calling the function with

let
    //Load XML file
    Source = Xml.Tables(File.Contents(oldFilePath)),
    //Call the ExpandAll function to expand all columns
    Output = ExpandAll(Source)
in
    Output

I get an expression error

Cannot convert value "" to type Table.

How do I skip such blank values?

please see above code, error message and screenshots

Answer 1

first of all, thanks for sharing your code, I was looking for it.

I got the same issue and resolved adding one step to your code:

    ...
    ColumnName = Table.ColumnNames(TableToExpand){ActualColumnNumber},

    **ReplaceEMPTY = Table.ReplaceValue(TableToExpand,"",null,Replacer.ReplaceValue,{ColumnName}),**

     //Get a list containing all of the values in the column
     ColumnContents = Table.Column(**ReplaceEMPTY**, ColumnName)
     ...

The Function with this added on:

let
    // http://blog.crossjoin.co.uk/2014/05/21/expanding-all-columns-in-a-table-in-power-query/
    //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},
      
      //ADDed to the original code to transform blank cells, now the Columns can be expanded as table
      ReplaceEMPTY = Table.ReplaceValue(TableToExpand,"",null,Replacer.ReplaceValue,{ColumnName}),**

      //Get a list containing all of the values in the column
      ColumnContents = Table.Column(ReplaceEMPTY,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

Hope it works for you!!!