Unpivot ANY nested table and load to Power BI Desktop

Earlier today I shared with you a function query here that unpivots ANY nested table into Excel and transforms summarized tables into a PivotTable. This post will show you how to import the function query and use it in Power BI Desktop on any table you load from Excel.

It is a common scenario to have nested/summarized tables in Excel that you need to unpivot before you build the dashboard with Power BI Desktop. With the function query which is described here, you can load ANY nested table to the Power BI Desktop and transform it to a format you can start working with.

Open Power BI Desktop, expand the Get Data menu, and click Blank Query.

1738-1-01

Click Advanced Editor, paste the M expression below, and then click Done:

(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

Now import this workbook to your Power BI Desktop.

In the Navigator dialog, select Sheet1 and click OK.

0702-1-05

The following table will be shown. You must agree that there is nothing you can do with this type of table structure 🙂

3157-1-06

Rename the function query Query1 to FnUnpivotNestedTable, and click the fx button in the formula bar.

Paste the formula below and press Enter:

= FnUnpivotNestedTable(#”Changed Type”, {“Continent”, “Country”, “City”}, {“School Type”, “Gender”, “Class”})

1-09

Change the Value column to Whole Number, and click Close & Apply.

That is it. You are now ready to build amazing reports with the data. You can see all the relevant fields in the Fields Well of the Reports view, and select your favorite visual.

Enjoy 🙂

1-12

6 comments

  1. Henk Hoogewerff Reply

    I reveive an error half way down at …”Table.RenameColumns(TransposeBackTable,……the error is “Token Equal expected” after the “s” from Table.RenameColumns. Have you any idea why? I am currently working with the latest Power BI desktop (August update 2017).

    • Gil Raviv Post authorReply

      There was an error in my code above. I fixed it. Can you try again?

      • eric

        What was the error you fixed Gil? I have the same error (with my own code) after a Table.RenameColumns() function.
        Thank you!

      • Gil Raviv Post author

        I suspect I may need to recheck the steps again. Due to recent changes in Power Query, an extra step of Promoted Headers is added when you import name ranges or worksheets. As a result, if you see a promoted headers step, you should delete the last two steps in Applied Steps right after you import the table.

  2. Anonymous Reply

    Thanks so much for posting this but when I followed your instructions I get the following error:
    Expression.SyntaxError: Invalid literal.
    0001: F= FnUnpivotNestedTable(#”Changed Type”, {“Continent”, “Country”, “City”}, {“School Type”, “Gender”, “Class”})
    —-> ^^^^^^^^^

    Any assistance would be greatly appreciated.

Leave a Reply