Search

# Unpivot and then pivot for clarity (case study)

Share

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.

and his boss wanted a report like this:

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

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

4. Pivot it to create the report we need.

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

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.

### 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

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

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.

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.

### How to calculate WEEKNUMBER in Month / Quarter / Year with Excel?

Let’s say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month or quarter? In this article, let me explain the logic and formulas we can use Excel for this.

## Related Tips

Keyboard Shortcuts

### 35 shortcuts & tricks to make you an #AWESOME Data Analyst

Chandoo.org Podcast Sessions

Learn Excel

Power BI

### How to “auto” generate calendar tables with Power Query – The best method

Charts and Graphs

Learn Excel

### 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"

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

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...

• Chandoo says:

Well, I saw your email the other day. Good thing you found the simple answer. 🙂

8. Rajesh Kumar SIngh says:

🙂

9. Cap D says:

.Kudos! God Bless You CHandoo!

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.