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

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.

Download the Excel File

Download the Excel file to follow along.

Unpivot Inventory Sheet With Power Query.xlsx (21.3 KB)

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.

Additional Resources

Please Leave a Comment

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

20 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon,I enjoy your great tips,the problem is that i m using my work PC of which I m restricted to use online therefore unable to watch videos or webinars.Can I save your video files without watching them live?

  • When i close and load the query sheet it gives me this msg ” data cannot be retrieved from the database ” and stop loading

  • Hi Jon,
    When adding a new column for Juice as your video lecture, instead of including Juice into product column, it created a new column for Juice when I hit refresh on other sheet.
    Please advise.
    Thanks Jon.

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

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

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

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

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

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

  • John,

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

    Woody

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar