fbpx
Search
Close this search box.

Unpivot and then pivot for clarity (case study)

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

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