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.
- Load the data in to Power Query using From Table feature.
- Unpivot all the monthly data columns
- Load the data in to Excel as a new table
- 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.
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.
- Introduction to Power Query
- Import and work with web data using PQ
- Unpivot data with Power Query – an example
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.
13 Responses to “Unpivot and then pivot for clarity (case study)”
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/
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
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.
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.
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.
That is Smart! Sunnykow
TQ Chandeep 🙂
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)
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.
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...
Well, I saw your email the other day. Good thing you found the simple answer. 🙂
🙂
.Kudos! God Bless You CHandoo!