Generalised unpivoting

Overview

This article explains how to create a generalised solution for unpivoting tables with any levels of hierarchies.

The general steps for achieving this are illustrated below:

Next, we show what happens in detail, step by step:

Steps

1. Load the Data

  • Get to the source data. Here, the source data is a sparse table with multiple hierarchies across columns and rows – and basically in a form that is not that useful for reporting.

2. Fill Down text columns

  • Apply the Fill Down columns 1, and 2

3. Merge text columns

  • Next, merge columns 1, 2, and 3, using a Colon (:) as the separator

4. Transpose the data

  • After this, Transpose the Data Table, as shown below:

5. Fill Down on anchor columns

  • Next, apply the Fill Down function to Columns 1, and 2

6. Promote Headers

  • Then, Promote Headers

7. Unpivot the data

  • After this, choose UnpivotOtherColumns, on the main anchor columns “::Color”, “::Parent Category”, and “Country:State/Region:City…”

8. Split columns

  • Then, split the Attribute column, using the Delimiter, Colon (:)

9. Rename columns

  • After this, rename columns to something more relevant and meaningful.

You are DONE!

10. Close & Load

  • Load the results into worksheet

The “sparse” summary table is now transformed into something that can be readily, and easily be put to better use.