This is the third 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 the first post here, I showed you how to transform a simple table into a PivotTable. In the next post here we moved one step further to transform a class schedule into a PivotTable. The complexity in that example was rooted in the fact that the range had merged cells with multiline data.
In today’s post we will move one step further, and transform a nested table into a PivotTable. The following screenshot shows you the format we will start with. We have a nested table with fake sales data by Country, Product, Year and Month. This is quite a common format. When you stumble upon such a table, you wish the author would use a PivotTable instead.
So our goal is to transform this table into a PivotTable like this one:
Want to jump straight to the solution?
Download this workbook.
Ready to start?
Download from workbook “Unpivot Nested table – Start Here.xlsx” from here, open it with Excel 2016, then select any cell in the range, and click From Table in the Get & Transform section of the Data tab.
In the Create Table dialog, uncheck My table has headers and click OK.
The Query Editor will appear. This is where we will perform all the transformations that are needed to unpivot the data and convert it to the required structure for PivotTable.
The first transformation is to fill down the values of the country column.
Right click on the header of Column1, select Fill and then click Down.
The next step is to fill the year values and merge it with the month. To do it we will need to transpose the data.
In Transform tab, click Transpose.
Now we have the year in the first column. We will perform Fill Down transformation.
Right click on the header of Column1, select Fill and then click Down.
Now we should convert the years to text, merge it with the month column and create a date column.
Select the header of Column1, and in the Transform tab, click Data Type: Any, then click Text.
Select the first two columns, right click on one of the headers, and click Merge Columns.
The Merge Columns dialog will appear. Select Space as Separator, type “Date” in the New column name (optional) box, and click OK.
The first two cells in column Date contain the headers we will need: Country and Product. However, due to the last merge operation, a leading space character was added to the values Country and Product. To remove the space character we will perform a trim transformation on the column.
Right click on the header of column Date, click Transform and then click Trim.
Now are ready to transpose the data back, so the year and month dates will be represented as columns.
In the Transform tab, click Transpose.
Click the table icon that is marked below, and then click Use First Row As Headers. Alternatively, you can find that command in the Transform tab.
Select the first two columns: Country and Product, right click on one of their headers, and in the context menu click Unpivot Other Columns.
That’s it. We finally have our data in the right structure. We will just need to rename the columns Attribute and Value to Date and Sales, change their corresponding format to Date and Decimal Number, and load the data to a new worksheet.
After you rename the columns and change their format, click Close & Load in Home tab.
The transformed data will appear on a new worksheet. Select one of the cells, and in the Insert tab, click PivotTable.
Click OK in the Create PivotTable dialog, and build your PivotTable. For example, you can drag and drop Country and Product to the ROWS section of the PivotTable Fields pane, the Sales to the VALUES section and the Date to the COLUMNS section. Excel will automatically restructure your information into 3-level hierarchy of Year, Quarter and Month.
Now whenever you have new entries in the original table (e.g. new columns for the year 2016, or new rows for countries and products), you can click Refresh All in the Data ribbon, and the query will transform the data again into the desired structure and format, that will be available in your PivotTable.
Download the workbook here to view the solution.
In the next post I will show you how to handle nested tables with 3-level of hierarchy on both rows and columns. Stay tuned 🙂
