How to Unpivot in Excel with Power Query + Video Tutorial
17

How To Unpivot in Excel with Power Query + Video Tutorial

Bottom line: Learn how to use Power Query to unpivot or normalize your data to get it ready for use in a pivot table.

Skill level: Intermediate

Unpivot Data and Reports with Power Query in Excel

Why Do I Need to Unpivot My Data?

Often times we receive data that is NOT in the right format for use in a pivot table.

This could be a report that is exported from a system of record, or it could be a spreadsheet that is being manually updated.  Either way, getting this data in the correct layout is the most important step in creating a pivot table.

Pivot Table Source Data Comparison

I have other articles that explain why the structure of the source data is so important and how pivot tables work.  Both these articles should help you understand why this process is so critical.

Power Query’s Unpivot Button

Fortunately, Power Query has an Unpivot Button that makes it really fast and easy to unpivot in Excel.

Unpivot Columns Button on the Power Query Editor Ribbon

It’s basically a 3-step process:

  1. Load your data into Power Query (create a query)
  2. Unpivot the selected columns to normalize the data.
  3. Output the results to a new worksheet in Excel.

The Unpivot Columns button is located on the Transform tab of the Power Query Editor window.

Checkout my article on an Overview of Power Query if you haven’t heard of this awesome (free) tool.

A Simple Example of Unpivot

The following screencast shows how to unpivot a sheet that contains inventory data.

Unpivot with Power Query in Excel Screencast2

 

The original sheet is laid out in a horizontally with columns for the different products.  To use this data as the source of a pivot table, we need to put all the product names in a single column and all the inventory values in another column.

Unpivot Product Columns - Excel Power Query Example

The Unpivoted Table in the image above can be used as the source data of a pivot table.  This layout will make it easy to create summarized reports with different layouts.

Video: How to Unpivot Your Data with Power Query

In the following video I show a simple example of how to unpivot your data with Power Query in Excel.

Video best viewed in full screen HD.

What Does Your Data Look Like?

This inventory sheet is obviously a really simple example.  However, Power Query can handle much more complex cases.

I want to know what your data looks like, and help you unpivot it with Power Query.  Does you data look like any of the following?

Unpivot Excel Reports - What Does Your Data Look Like

Vote Here

Vote for the example that looks like your data structure.

  • Look at the examples in the image above vote for the ones that look like your data.

Additional Resources

Please Leave a Comment

Please leave a comment below with any questions.  Thank you!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 17 comments
Bob Lassiter - January 19, 2017

Jon – very helpful! Most of our data was already formatted by month across columns. We spent a lot of time converting the data into a list to then use pivot tables for reporting and analysis. We used your index-match method, and also developed VBA code to accomplish the same, but the new unpivot function in Excel Get & Transform will greatly reduce time.

Reply
    Jon Acampora - January 20, 2017

    Thanks Bob! I completely agree. I have developed several macros to accomplish this task, but Power Query can easily handle most of relatively simple transformations.

    Reply
Abdelrahman omer - December 5, 2016

Nice tutorial, very clear thank you

Reply
Duc Q Nguyen - September 2, 2016

Thank you so much! I have known about Power Query before but I don’t expect that it is so helpful. I usually using a model formula to transform data using INDEX, MATCH, MOD, VLOOKUP function but now it is so easy. Great to know.

Reply
Elmarie - May 19, 2016

Am interested in Power Query and presently have Office Professional 2010 – NOT Professional Plus 2010, so as I understand it, Power Query cannot be installed. Which do you recommend for an upgrade – 2013 (and download the PQ add-on) or 2016 with it included in Office 365 ProPlus?

Reply
    Jon Acampora - May 21, 2016

    Hi Elmarie,
    I would first try to download and install Power Query for your version of Office. I have heard that it works for some users on different versions of 2010. If you are going to upgrade then I would go with the latest and greatest, 2016. Microsoft will continue to roll out new features and functions for 2016. They are already releasing updates with new functions and charts. This is something we have never seen in a previous version of Excel, and I think the constant updates will make Excel a lot better going forward. I hope that helps.

    Reply
Sam - March 22, 2016

Hi Jon, I have 2010 professional and I have installed Power Query but I can’t get past step one being when I’m in a pivot table the insert table is greyed out ;(
It not when I’m outside the pivot table.

Any ideas?

Thanks,

Sam.

Reply
    Jon Acampora - March 24, 2016

    Hi Sam,
    The Power Query Table button only works for importing regular Tables, not Pivot Tables. You can convert the source data of your pivot table to a Table, then import it to Power Query. I hope that helps.

    Reply
RJ - July 5, 2015

Thanks Jon…2 great tutorials (including the one on using Index to do the same)…much appreciated!!

Reply
Bob - April 22, 2015

Nice tutorial of what is a common problem I am sure. Thanks Jon.

Reply
Woody Torres - April 1, 2015

John,

Kudos! This is an excellent and very practical example of how to use Power Query. Thank you very much.

Woody

Reply
Hossat - April 1, 2015

I was working on a VBA procedure to do that.
But it’s very easy with Power-Query.
Thank you very much Jon

Reply
    Jon Acampora - April 1, 2015

    Thanks Hossat! I developed a VBA add-in once that did this as well. But you’re right, Power Query makes it much easier. 🙂

    Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x