Unpivot and then pivot for clarity (case study)

Posted on March 8th, 2016 in Pivot Tables & Charts , Power Query - 13 comments

Or more appropriately titled, the one where Power Query solves the problem in less time than it takes you to say Get & Transform Data.

Recently, one of my students Mr. K, sent me a pivot table problem.

Today my boss asked me “how much we paid to staff since the inception of our business with their respective date of joining?” He wanted to know, level wise summary of the last 16 years (on Quarterly / Year wise basis).

The records appended from the database month wise. Have a look to the file and give your ideas.

Mr. K’s data looked like this.

payroll-data-original-pq-casestudy

and his boss wanted a report like this:

payroll-data-report-required-format

What now?

The obvious solution – VLOOKUPs and patience

Even before I could go thru Mr. K’s problem and reply to him, he wrote back to me saying that he found the solution. 

He used what I call as VLOOKUP and patience method. First he unpivoted original data using 233,000+ VLOOKUPs . Then he created a new pivot table from this unpivoted data to answer his boss’s questions.

While this method is fine, it consumes a lot of time and coffee.

A better solution – Power Query and pop corn

We, humans are not evolved to write two hundred thousand VLOOKUPs in afternoons. We are better at building powerful machines, watch them do bad ass work while we chew delicious pop corn.

So why not let the computer solve the problem?

Here is the ridiculously simple four step tutorial to get the same results.

  1. Load the data in to Power Query using From Table feature.
  2. Unpivot all the monthly data columns

    unpivot-monthly-data-pq

  3. Load the data in to Excel as a new table

    payroll-data-after-unpivoting

  4. Pivot it to create the report we need.

    payroll-data-after-pivoting

All this takes less than 15 seconds. Whenever you have new data, simply refresh the connection and everything (PQ, Pivot tables) gets updated.

Download the example workbook

Here is the workbook just in case you need to see everything. Please use Excel 2013 or above to play with this.

More power to you

Power Query is a truly remarkable feature of Excel. It saves you a lot of time and hassle. If you are new to Power Query (also known as Get & Transform data in Excel 2016), check out below tutorials to get started.

How would you solve this?

Prior to Power Query, I would have used VBA (as there is a lot of data) for something like this. For smaller data sets, I would have used VLOOKUPs.

What about you? What method would you use to rearrange data like this? Please share your tips in the comments section.

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

13 Responses to “Unpivot and then pivot for clarity (case study)”

  1. Chandeep Chhabra says:

    Hi Chandoo, I got the same query probably form the same gentleman but I tweaked the data a bit. Here is my solution http://www.goodly.co.in/how-to-re-arrange-data-for-a-pivot-table/

  2. Justin N says:

    For those unable to use power query, below is a script that will perform the same function. It will ask you how many attributes of fields you have before your time dimension members and pivot on the time members.

    Sub ConvertToPivotFormat()
    Dim SummaryTable As Range
    Dim OutRow As Long
    Dim r As Long, c As Long, x As Long

    x = InputBox("How many Fields?")

    ActiveSheet.Name = "Source"
    Sheets.Add.Name = "Summary"

    Set OutputRange = Sheets("Summary")
    Set SummaryTable = Sheets("Source").Range("A1").CurrentRegion

    OutRow = 2

    For r = 2 To SummaryTable.Rows.Count

    For c = x + 1 To SummaryTable.Columns.Count

    For c1 = 1 To x
    OutputRange.Cells(OutRow, c1) = SummaryTable.Cells(r, c1) 'fields
    Next c1

    OutputRange.Cells(OutRow, x + 1) = SummaryTable.Cells(1, c) 'months
    OutputRange.Cells(OutRow, x + 2) = SummaryTable.Cells(r, c) 'amounts

    OutRow = OutRow + 1

    Next c
    Next r

    'add Headers on Summary Sheet
    Sheets("Source").Range("A1").Resize(1, x).Copy Sheets("Summary").Range("A1")
    Sheets("Summary").Range("A1").End(xlToRight).Offset(0, 1).Value = "Month"
    Sheets("Summary").Range("A1").End(xlToRight).Offset(0, 1).Value = "Amount"

    End Sub

  3. Gary Ferguson says:

    Maybe I totally missing something, but on the Raw Data tab I just created a new column in the table that was a sum of all the salary months for that row. I then created a pivot over this table using YOJ as the column, Grade as the Row, and this new sum column in the Values and it looks just like the Pivot tab.

    Love the podcast by the way.

    • David says:

      Thanks Gary, I would agree that this is the 'obvious' method..., which also takes seconds! Truly 'ridiculously simple' compared to over engineered solutions mentioned above. Simpler is best.

  4. SunnyKow says:

    I would use the reverse pivot method just like Chandeep except I would do the following to the raw data first:
    1) Replace all blank salaries cell with 0
    2) Use a helper column (insert in column 5) to merge the first 4 columns, separated by a semi colon and then copy and paste the helper column as value.
    3) Delete the first 4 columns
    4) Press ALT+D+P to create the PT using the multiple consolidated ranges method and then drill down the output's grand total
    5) You will need to use "text to column" to split column A in the drilled down worksheet (delimited by a semi colon)
    You can now use the "new" raw data to do the final Pivot
    This method may seems long but it has always worked for me.

  5. SunnyKow says:

    I believe the data in the workbook supplied for this exercise is not "clean". Many of the blank salary cells are not truly empty and can cause problem in the Pivot. That is why F5-Goto-Special-Blanks to fill the blank cells will not work (only 2 cells selected). I ended up doing a search and replace instead.
    My final raw data output ends in row 233724 while Chandoo's ends at 233722 (difference of 2)

  6. Nishat Sharma says:

    It's good use of Pivot Table, but use make this pivot very descriptive and easy to get. Just make slabs.

    And you can make this filtered and summed data by 'COUNTIFS' and 'SUMIFS'. try to use these and see when any data values changes in orginal excel you do not need to refresh the WorkBook like you need in pivot.

  7. Hui... says:

    I rarely use Pivot Tables
    and then this week a Client requires assistance with unpivotting a table of data with Monthly columns and Voila here is the solution

    Karma is with me this week

    Thanx Chandoo

    Hui...

  8. Rajesh Kumar SIngh says:

    🙂

  9. Cap D says:

    .Kudos! God Bless You CHandoo!

Leave a Reply