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.

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.

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

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”})

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 🙂