Unpivot and then pivot for clarity (case study)

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.

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.