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

## “Necessity is the mother of invention…”

4 min readMar 2, 2017

--

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

# 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 automatically detects the data table — in this case 14 rows x 35 columns (quarterly sales from Dec 2007 through June 2016).

## Transform the Query to Shape our Data Set

• 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”

## 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… :-)

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

“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!

## Connect with Don!

Or, just Google me…I’m everywhere

Send Don an email

--

--

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics #genai #chatgpt