MS Excel — I Finally Get It! Unpivot in Power Query is Awesome!

“Necessity is the mother of invention…”

Don Tomoff

Follow

Published in

Let’s Excel

4 min readMar 2, 2017

--

**Microsoft Excel — Power Query Unpivot!**

My Situation that Required Unpivot

I was neck deep in an analysis project this week and had the following dilemma…

“How do I convert an Excel data set that is 125 rows x 45 columns into a proper data set for analysis?”

In business, this is not an uncommon situation. Many times, data is organized in “report” format — descriptions on the left with columns of data, such as this sample report:

So, I did a Google search — “convert Excel columns to rows in Power Query”.
TIP #1 — Doing a Google search — whenever you have a question — is my #1 productivity tip. The ability to access information and know how is simply insane!

Understand the Basics of Power Query Unpivot

My go to resource for anything Excel is the YouTube ExcelIsFun channel. So I searched Unpivot and came across this brief 6:15 video. Review this for the fundamentals — it’s a very simple example to get you grounded.

TIP #2 — Subscribe and get familiar with the ExcelIsFun YouTube channel. Mike Girvin is excellent!

ExcelIsFun Power Query to Unpivot Data — https://www.youtube.com/watch?v=YuoEegbrW1I&list=PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ&index=36

Example Dataset — Let’s Step Through an Unpivot Scenario!

Using the Apple sales analysis report sample, we will step through how to convert this to a dataset necessary for analysis purposes.

Access Power Query and identify the Data Set

Power Query, or “Get And Transform” (Excel 2016) add-in needs to be installed (see “The Complete Guide to Installing Power Query”)
Click on Power Query and select data source (“From Table” in this case)

Click on Power Query and select data source

Power Query automatically detects the data table — in this case 14 rows x 35 columns (quarterly sales from Dec 2007 through June 2016).

Put cursor inside data set — Power Query detects data table

Data set is converted to a Table and Power Query “Query Editor” opens

Transform the Query to Shape our Data Set

Add column header for Product Category and Rename “Applied Steps” to be descriptive

Highlight columns to Unpivot, select “Transform” and then “Unpivot Columns”

Unpivoted data set — now we are almost ready for data analysis

Headers renamed → Select “Home” tab → “Close & Load”

In this case, I want to load the results into a worksheet in Excel (versus a “Data Model”), so I click “Close & Load”.
I now have my data “analysis ready”

Apple Quarterly Sales — Data analysis “ready”

CORRECTION!

NOTE: For analysis to work correctly, the date format IMPORTED into the data set needs to be “xx/xx/xxx”, like follows (and converted from “TEXT” to “DATE” format in Power Query):

SORRY FOR THE OVERSIGHT!

Let’s Analyze!

Now that the data is in a analysis format, let me show you just a few ideas of what is possible to quickly pull from this data set!

1 — “What are iPhone sales by year?”

I have results through June 2016, so it is not totally complete… :-)

iPhone Sales by Year — notice that categories changed

2 — “What are iPad sales by year?”

3 — “Combined iPad and iPhone sales by year?”

About Don

“On a mission to challenge the status quo to a more productive and effective end…”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

One lesson learned over the years is that all of us, regardless of organization type or size, struggle with similar issues — primarily information management, organization, presentation, and effective use of our time. Let’s change that…one person at a time!