(By: Gil Raviv. Originally published here).
Get ready to be amazed 🙂
This is the fourth post in the series The Definitive Guide to Unpivot with Power Query in Excel.
In this series we walk you through one of the coolest data transformation features in Excel – The Unpivot transformation. Powered by Power Query technology, the Unpivot transformation is available for everyone using the new Get & Transform section of the Data tab in Excel 2016, or as an Add-in in previous versions of Excel.
In my last post here, I showed you how to transform a nested table into a PivotTable. In today’s post we will move one step further, and learn how to transform ANY nested table into a PivotTable. Why ANY? Because no matter how many fields you have as nested rows and columns, you will be able to transform it to a PivotTable if you follow today’s walkthrough.
We will start with a specific scenario, and then share with you a more generic function query which uses Power Query expression (M) to address any type of nested table.
Let’s start with a table that uses Continent, Country and City as row fields, and School Type, Gender and Class as column fields:
Our goal is to unpivot & transform this 3×3 dimension table above the following table which can be then used by PivotTables and PivotCharts: