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 🙂
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).
There was an error in my code above. I fixed it. Can you try again?
What was the error you fixed Gil? I have the same error (with my own code) after a Table.RenameColumns() function.
Thank you!
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.
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.
Can you copy the entire code from Advanced Editor?