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:

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

2 comments

  1. Henk HoogewerffReply

    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?

Leave a Reply