Transform ANY nested table to Pivot Table with function query

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:

Let’s start

Download the workbook from here, open it with Excel 2016 (You can also perform the steps in Excel 2010 and Excel 2013 after you install Power Query Add-In).

Select any cell in the range, and click From Table in the Get & Transform section of the Data tab (or in Power Query tab if you use the Add-In in older versions of Excel).

Note: If you use this tutorial on your own table, the use of From Table will convert the range into a Table. If you don’t want to turn the range into a table,you can assign a name to the range, before you click From Table. To assign a name to a range, select the range and assign it with a name in the highlighted box:

In this step we will fill the missing values for the continent and country columns.

In the Query Editor, select the first two columns, right click on one of the headers, click Fill, and then click Down.

The next step is to fill the null values of first two rows with School Type and Gender values. Since we don’t have a Fill Right command we will transpose the table, and then apply Fill Down transformation on the School Type and Gender headers.

In the Query Editor, go to the Transform tab and click Transpose.

Select the first two columns that represents the School Type and Gender, right click on one of the headers, and select Fill then click Down.

In the next step we will merge between the first three columns that represent School Type, Gender and Class, so we will later be able to use the combinations of School Type/Gender/Class as headers.

Let’s assume that we can use the semicolon character as a separator in the merge operation.

Note: If your tables contains a semicolon characters in the header names, you should select a different character than the semicolon as the separator.

Select the first three columns, right click on one of the headers, and click Merge Columns. 

In Merge Columns dialog, select Semicolon as the Separator, and click OK.

Now let’s transpose back the table, and use our merged School-Type;Gender;Class as headers.

In the Transform tab, click Transpose, and then click Use First Row As Headers.

Let’s rename the first three columns to Continent, Country and City.

(To rename a column header simply double click on its label, and type the new name, then press Enter or click outside of the header).

Now we are ready to the Unpivot magic –

Select the columns Continent, Country and City, right click on one of the headers and click Unpivot Other Columns.

Select the Attribute colomn, and click Split Column in Home tab, then click By Delimiter. Select Semicolon as separator and click OK in the Split by delimiter dialog.

Rename the highlighted column headers to School Type, Gender and Class. You can also change the Value column to Grade.

Finally, in Home tab, click Close & Load.

That’s it. We got the desired format as a new table in the workbook. Now we can create a PivotTable and re-pivot the data as we see fit.

In the next section we will discuss how to apply the same kind of transformation to any kind of nested table.

The Generic Function Query

Now let’s move the generic solution. As we stated at the beginning of this tutorial, we want to perform this unpivot transformation on nested tables with ANY number of nested row and column headers.

Based on the transformation we did previously, I created a function query, FnUnpivotNestedTable, that perform the same sequence of transformations on any kind of nested table. The function query can be found in our workbook.

To see an example of a query that uses this function, open the workbook here, click Show Queries, and double click on the query How To Use The Function.

If you have a workbook with a nested table, and would like to apply the function above on your table, perform the following steps:

To copy and paste the function query:

1. Open this workbook.
2. In Data tab, click Show Queries.
   
3. Double click on the function query FnUnpivotNestedTable.
4. In the Query Editor, click Advanced Editor.
   
5. Copy the entire code from the Advanced Editor dialog, click Done, and then close the Query Editor.
   
6. Open your workbook.
7. In Data tab, click New Query, then click From Other Sources and click Blank Query.
8. In the Query Editor, click Advanced Editor.
   
9. Paste the code from step 5, and click Done.
   
10. In the Query Editor, Query Settings pane, rename the query to FnUnpivotNestedTable.
11. In Home tab, click Close & Load.

To use the function query on your nested table:

1. Select your nested table.
2. In Data tab, click From Table.
3. In the Create Table dialog, uncheck the box My table has headers and click OK.
4. In the Query Editor remove subtotal and total rows and columns. We didn’t discuss this step in this tutorial, but this is a basic step. Use the filtering functionality and the Remove Top/Bottom rows commands to remove unnecessary rows, and delete columns which has subtotals or totals.
5. Click Advanced Editor and add the FnUnpivotNestedTable formula. For example:
   Result = FnUnpivotNestedTable(#”Previous step”, {“Continent”, “Country”, “City”},{“School Type”, “Gender”, “Class”})

Here is a screenshot of the Advanced Editor with the example from our workbook. The second command is the call for the function FnUnpivotNestedTable. It requires 3 parameters:

1. The table to unpivot
2. The row headers as a list – On our example: {“Continent”, “Country”, “City”}
3. The column headers as a list – On our example: {“School Type”, “Gender”, “Class”}

Note: You can apply any number of nested row headers and columns. If you have a single row header and multiple nested columns (or vice versa), it will work as well.

Next – How we created FnUnpivotNestedTable