FnUnpivotNestedTable


On the previous page of this tutorial, we used a function query FnUnpivotNestedTable to unpivot ANY nested tables. In this section we will drill down to the function itself, and show you how we created it.

Note: This section is dedicated for advanced users of Power Query, and requires moderate knowledge of the Power Query formula language (M). If you feel this section is not relevant for you, you can still use the tutorial on the previous page.

We will start with the auto-generated M code that was created when in this tutorial when we transformed the table in this workbook through the Query Editor.

Let's highlight all the sections in the code that were specific for the table we used and transformed the expression to a generic one.


let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Column1", "Column2"}),
    #"Transposed Table" = Table.Transpose(#"Filled Down"),
    #"Filled Down1" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down1",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1"),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{";;", "Continent"}, {";;_1", "Country"}, {";;_2", "City"}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Continent", "Country", "City"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attribute",Splitter.SplitTextByDelimiter(";"),{"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "School Type"}, {"Attribute.2", "Gender"}, {"Attribute.3", "Class"}})
in
    #"Renamed Columns1"


To start our function, let's add the function definition. The function will accept a table, and a list of row and column header names (e.g. {"Continent", "Country", "City"} and {"School Type", "Gender", "Class"} ).


Before:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

     ...

in
    #"Renamed Columns1"


After:

(Source as table, RowHeaders, ColumnHeaders) =>
let

  ...

in
    #"Renamed Columns1"


We will now handle the Fill Down transformation, by creating a dynamic list that contains the first N-1 column names


Before:

#"Filled Down" = Table.FillDown(Source,{"Column1", "Column2"}),


After:

//Get a list of the first n column names of a table
GetFirstHeaders = (src as table, n as number) as list => List.Range(Table.ColumnNames(src), 0, n),

RowHeaderCount = List.Count(RowHeaders),
ColumnHeaderCount = List.Count(ColumnHeaders),
    
//The row headers we should apply fill down
FillDownRowHeaders = GetFirstHeaders(Source, RowHeaderCount - 1),

//Apply Fill Down on row headers
FilledDownRowFields = Table.FillDown(Source,FillDownRowHeaders),


Now let's handle the transpose and second fill down transformation.

The challenge here is to create a dynamic list of "Column1", "Column2", ..., "ColumnN-1" based on the number of items in the Column Headers that were received as a parameter list (e.g. {"School Type", "Gender", "Class"} ). Note that we create here a list of "Column1", "Column2", etc. and only use the input list to determine the first columns we need to fill down.


Before:

#"Transposed Table" = Table.Transpose(#"Filled Down"),
#"Filled Down1" = Table.FillDown(#"Transposed Table",{"Column1", "Column2"}),


After:

//Transpose table
TransposedTable = Table.Transpose(FilledDownRowFields),

//This line will be called later for a merge operation. For now all we need to know that it contains a list of "Column1", "Column2", ..., "ColumnN".
ColumnHeadersToCombine = GetFirstHeaders(TransposedTable, ColumnHeaderCount), 
   

//The column headers we should fill down
FilledDownColumnHeaders = List.Range(ColumnHeadersToCombine, 0, ColumnHeaderCount - 1),

//Apply Fill Down on column headers
FilledDownColumnFields = Table.FillDown(TransposedTable, FilledDownColumnHeaders),


The next three step is relatively easy. At the previous step we built a list of "Column1", ... "ColumnN", we will use it in the CombineColumns step.


Before:

#"Merged Columns" = Table.CombineColumns(#"Filled Down1",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
#"Transposed Table1" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1"),


After:

//Merge columns with a semicolon delimiter
MergedColumns = Table.CombineColumns(FilledDownColumnFields, ColumnHeadersToCombine, Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    

//Transpose the table back
TransposedBackTable = Table.Transpose(MergedColumns),
    

//Promote first tow as headers
PromotedHeaders = Table.PromoteHeaders(TransposedBackTable),
   


Now comes the most complex code to build the second parameter for the RenameColumns step. We will combine between the actual column names, and the new names in ColumnHeaders.


Before:

#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{";;", "Continent"}, {";;_1", "Country"}, {";;_2", "City"}}),


After:

//In this section we build the necessary text and lists that will allow us to rename the columns to the values in RowHeaders
//Here was the original step that we will dynamically build:
//RenamedRowHeaders = Table.RenameColumns(TransposedBackTable,{{";;", "Continent"}, {";;_1", "Country"}, {";;_2", "City"}}),

CurrentColumnsToRename = GetFirstHeaders(PromotedHeaders, RowHeaderCount),
HelperListForRowHeaders = Table.ToRows(Table.FromColumns({CurrentColumnsToRename, RowHeaders})),       
     
//Here we rename the columns that contains the input Row Headers
RenamedRowHeaders = Table.RenameColumns(PromotedHeaders, HelperListForRowHeaders), 


In the last two steps, we apply UnpivotOtherColumns on the first columns that now contains the original Row Headers (e.g. Continent, Country & City). We can use the input RowHeaders list to replace the static column names. Then we can perform the SplitColumn operation and use the original ColumnHeaders list instead of {"Attribute.1", "Attribute.2", "Attribute.3"}. By doing so, we no longer need the Rename Column step.


 

Before:

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Continent", "Country", "City"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attribute",Splitter.SplitTextByDelimiter(";"),{"Attribute.1", "Attribute.2", "Attribute.3"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "School Type"}, {"Attribute.2", "Gender"}, {"Attribute.3", "Class"}})
in
    #"Renamed Columns1"


 

After:

    //Here we perform the unpivot step
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(RenamedRowHeaders, RowHeaders, "Attribute", "Value"),

    //Here we split back the Column Headers
    SplitColumnByDelimiter = Table.SplitColumn(UnpivotedOtherColumns,"Attribute",Splitter.SplitTextByDelimiter(";"), ColumnHeaders)

in

    SplitColumnByDelimiter


That's it, we modified the original code into a function that can accept any kind of nested table and perform the unpivot operation on it.

Here is the new function:

(Source as table, RowHeaders, ColumnHeaders) =>
let
 
    // Get a list of the first n column names of a table
    GetFirstHeaders = (src as table, n as number) as list => List.Range(Table.ColumnNames(src), 0, n),

    RowHeaderCount = List.Count(RowHeaders),
    ColumnHeaderCount = List.Count(ColumnHeaders),
   
    //The row headers we should apply fill down
    FillDownRowHeaders = GetFirstHeaders(Source, RowHeaderCount - 1),

    //Apply Fill Down on row headers
    FilledDownRowFields = Table.FillDown(Source,FillDownRowHeaders),

    //Transpose table
    TransposedTable = Table.Transpose(FilledDownRowFields),

    //The columns that we will merge together
    ColumnHeadersToCombine = GetFirstHeaders(TransposedTable, ColumnHeaderCount), 
   
    //The column headers we should fill down
    FilledDownColumnHeaders = List.Range(ColumnHeadersToCombine, 0, ColumnHeaderCount - 1),

    //Apply Fill Down on column headers
    FilledDownColumnFields = Table.FillDown(TransposedTable,FilledDownColumnHeaders),

    //Merge columns with a semicolon delimiter
    MergedColumns = Table.CombineColumns(FilledDownColumnFields, ColumnHeadersToCombine, Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
   
    //Transpose the table back
    TransposedBackTable = Table.Transpose(MergedColumns),
   
    //Promote first tow as headers
    PromotedHeaders = Table.PromoteHeaders(TransposedBackTable),
   
    //In this section we build the necessary text and lists that will allow us to rename the columns to the values in RowHeaders
    //Here was the original step that we will dynamically build:
    //RenamedRowHeaders = Table.RenameColumns(TransposedBackTable,{{";;", "Continent"}, {";;_1", "Country"}, {";;_2", "City"}}),
   
    CurrentColumnsToRename = GetFirstHeaders(PromotedHeaders, RowHeaderCount),
    HelperListForRowHeaders = Table.ToRows(Table.FromColumns({CurrentColumnsToRename, RowHeaders})),
   
    //Here we rename the columns that contains the input Row Headers
    RenamedRowHeaders = Table.RenameColumns(PromotedHeaders, HelperListForRowHeaders),

    //Here we perform the unpivot step
    UnpivotedOtherColumns = Table.UnpivotOtherColumns(RenamedRowHeaders, RowHeaders, "Attribute", "Value"),

    //Here we split back the Column Headers
    SplitColumnByDelimiter = Table.SplitColumn(UnpivotedOtherColumns,"Attribute",Splitter.SplitTextByDelimiter(";"), ColumnHeaders)
 
in
    SplitColumnByDelimiter


Hope you enjoyed this blog post.

Follow me on Twitter to get more cool stuff.

Comments (4)

  1. anonymouscommenter says:

    Prepare to be amazed 🙂
    This is the fourth post in the series The Definitive Guide to Unpivot with

  2. Roger Govier says:

    Excellent post Gil (as always) and nicely explained.

    One minor point, in your last colour coded AFTER section, you have left a trailing comma after the last line before IN, which will of course give rise to a syntax error.

    In your final new function code, you have removed it

  3. Gil Raviv says:

    Hi Roger,
    Thank you so much for the feedbacks. I fixed the error. Now I know how to get compliments – by keeping the errors in the code 🙂

  4. Errors in code can drive learners into a state of mind to drive them crazy 🙂

Skip to main content