Unpivot Nested Headings With Power Query

If you are a data analyst, you undoubtedly know and love Excel, and undoubtedly know and love pivot tables, they must be a core part of your analysis toolbox. No doubt at some time you will have faced the need to pivot some data that arrives on your desktop in a cross-tab format, thereby needing to be flattened before you can do meaningful analysis on that data. There have been various ways to achieve this in the past, but with the introduction of Power BI,  Power Query has a very useful facility to unpivot such a table. There are many articles scattered around the web, such as this one from Ken Puls and another by Mike Alexander, that show how to use Power Query to unpivot data, so I will not  discuss it any further here.

All of these articles show how to unpivot a ‘normal’ cross-tab report, such as the sales table shown in the image below.

Standard Table

But there is a problem should the horizontal groupings have two levels, such as Country within Region, as shown in the next image.

Multiple Headings Table

This is somewhat trickier. If you load and unpivot this table into Power Query, you get the following results.

Multiple table unpivotted

This is not too useful. What is actually wanted is a table that would look like this.

Final Results

Even though that table from Power Query can be tidied with some judicious filtering and column renaming, there is not much else that can be done with it.

So how can we unpivot such a table? It can be done, but we need to build our query in a number of steps, using the power of Power Query’s expression language.

I first came across this problem when Ingeborg Hawighorst, a fellow Excel MVP, mentioned that she was looking to do a presentation at ComCamp in Auckland in March, and had downloaded some stats on birth rates in New Zealand to use in this presentation. Unfortunately, this data has two levels of headings similar to the table above; Ingeborg needed to create a dataset  that could be used for further analysis with Power Pivot, Power View, and/or Power Map.

In the solution presented here, I will use this same dataset. The data is grouped by age bands within regions of New Zealand, and is listed by year. I will limit myself to unpivotting the data and loading it into the datamodel –  if you want to see it presented in Power View and Power Map, you’ll need to go to ComCamp.

The data is shown in the image below.

NZ Birth Rate stats

The birth rate data is stored in an Excel file, so the first step is to import the table into Power Query using the From File>From Excel button. The M expressions generated are:

Source = Excel.Workbook(File.Contents("C:\Users\Bob\Documents\My Blogs\Unpivot Nested Headings\NZ Birth Rates.xlsx")),
LoadData = Source{[Name="NZ Birth Rates"]},

Name is the table in the workbook to be loaded, and you will need to adjust the file path accordingly.

The second step is to transpose the table, move the regions and countries to row headings, and years to column headings. Here the M is:

TransposeTable = Table.Transpose(LoadData),

A bit of data cleansing is done next, remove the first row and rename some columns:

PromoteHeaders = Table.PromoteHeaders(TransposeTable),
RenameDimensions = Table.RenameColumns(PromoteHeaders , {{"Column1", "Region"}, {" ", "Age Band"}}),

The next step is where it really gets clever. We need to duplicate the region name for each of the age bands so that we can use it in Power Pivot. This is achieved using the FillDown method

FillDownRegion = Table.FillDown(RenameDimensions, {"Region"}),

After we have done this, the data is starting to take shape, as can be seen in the following image.

Multiple headings unpivootted

We now have data that we can unpivot. We want to unpivot all of the columns except Region and Ages. Again, M has a method for this:

UnpivotValues = Table.UnpivotOtherColumns(FillDownRegion, {"Region", "Age Band"}, "Attribute", "Value"),

The data is looking good at this point, but as you can see in the following image, it has a few rows that we don’t want, total rows that pivot tables will calculate for us.

Unpivotted data

All that is left to do some more data cleansing, filter out unwanted rows, and rename the year column:

FilterOutCountry = Table.SelectRows(UnpivotValues, each ([Region]<>"Total New Zealand")),
FilterOutAllAges = Table.SelectRows(FilterOutCountry, each ([Age Band]<>"Total all ages")),
RenameYearColumn = Table.RenameColumns(FilterOutAllAges, {{"Attribute", "Year"}})

We can now load this transformed data into Excel and use it within PowerPivot.

I added a couple of Excel tables to my datamodel to provide a richer pivot. The first was a Region table, which has the island that the region is within (for a slicer), as well as area and population, and an Age Band table so that the bands can be sorted in order/. The two tables are shown below.

Region table

Age band table

That about wraps it up. As I mentioned above, the presentation can be greatly enhanced as Ingeborg will be showing, but this post is about the Power Query, so we will stop here. The enhanced presentation should be great though, and I am sure that ComCamp will be a great event, so if you are in Auckland in late March, try to get there. I’m going later in the year, but about 8-9 months too late.

I should also give substantial credit to Rory Archibald, a UK Excel MVP, who took my first, very klunky approach and refined it into a more elegant, more focussed solution that properly exploited the power of M. I have developed that further into the solution presented; you are seeing the results of our collaboration.

The full M listing for this query is:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Bob\Documents\My Blogs\Unpivot Nested Headings\NZ Birth Rates.xlsx")),
    LoadData = Source{[Name="NZ Birth Rates"]},
    TransposeTable = Table.Transpose(LoadData),
    PromoteHeaders = Table.PromoteHeaders(TransposeTable),
    RenameDimensions = Table.RenameColumns(PromoteHeaders , {{"Column1", "Region"}, {" ", "Age Band"}}),
    FillDownRegion = Table.FillDown(RenameDimensions, {"Region"}),
    UnpivotValues = Table.UnpivotOtherColumns(FillDownRegion, {"Region", "Age Band"}, "Attribute", "Value"),
    FilterOutCountry = Table.SelectRows(UnpivotValues, each ([Region]<>"Total New Zealand")),
    FilterOutAllAges = Table.SelectRows(FilterOutCountry, each ([Age Band]<>"Total all ages")),
    RenameYearColumn = Table.RenameColumns(FilterOutAllAges, {{"Attribute", "Year"}})
in
    RenameYearColumn

You can download the stats data file here, and the full query and presentation workbook here.

Advertisements
This entry was posted in Power BI, Power Query and tagged , , , , , , . Bookmark the permalink.

9 Responses to Unpivot Nested Headings With Power Query

  1. Pingback: Excel is for Parameters | Bob's BI Ramblings

  2. Pingback: Need Help: Reshape Cross table in PowerPivot (unpivot)

  3. Thom says:

    Hi Bob,
    unfortunately, I cannot get this running…. TransposeTable throws the error
    “Expression.Error: Cannot convert a value of type Record to type Table.
    Details:
    Value=Record
    Type=Type”
    Don’t know, what is going wrong, here 😦

  4. Thom says:

    Hi Bob,

    it works that way after adding “Year” as column header in the first column and changeing the M script to:

    let
    Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
    TransposeTable = Table.Transpose(Source),
    PromoteHeaders = Table.PromoteHeaders(TransposeTable),
    RenameDimensions = Table.RenameColumns(PromoteHeaders , {{“Column1”, “Region”}, {“Year”, “Age Band”}}),
    FillDownRegion = Table.FillDown(RenameDimensions, {“Region”}),
    UnpivotValues = Table.UnpivotOtherColumns(FillDownRegion, {“Region”, “Age Band”}, “Attribute”, “Value”),
    FilterOutCountry = Table.SelectRows(UnpivotValues, each ([Region]”Total New Zealand”)),
    FilterOutAllAges = Table.SelectRows(FilterOutCountry, each ([Age Band]”Total all ages”)),
    RenameYearColumn = Table.RenameColumns(FilterOutAllAges, {{“Attribute”, “Year”}})
    in
    RenameYearColumn

    great work, anyway! As I’ve no glue on the M language, it is not clear to me why loading from an Excel file doesn’t work.

    Best regards,
    Thom

  5. Matt Gaskins says:

    I have no idea what an M script is. Will do it manually instead.

    • wessexbob says:

      The M script is created automatically if you create any Power Query steps using the UI. OIften it can all be done via the UI, but sometimes you have to go in and extend the M via the editor.

  6. vishnu says:

    how about if we have nested heading in both rows and columns… please advice…

  7. Gil Raviv says:

    Great post. For a generic function query that unpivots ANY nested table read this blog post http://blogs.technet.com/b/gilraviv/archive/2015/10/18/transform-any-nested-tables-to-pivot-table.aspx

  8. AOC says:

    This article helped: It solved my pesky problem. Thanks for taking the time to write the blog entry and share you knowledge with others.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s