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

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.

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!

23 comments

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

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

  • John,

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

    Woody

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

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

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

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

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

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

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

  • Hi Jon, Thank you so much for your lessons! Very informative.

    I am running into an issue. I unpivoted the values from the “consultant name” column to obtain two value types in the same column – values associated with the number of records touched by “date” columns and values associated with the “hours billed” column.
    Then I typed in a comma to the beginning of the hours billed column heading and used a delimiter to split the Hours billed to a different column . Unfortunately it leaves behind a null that once I pivot the the column it returns an error that “We cannot convert the value null to type text”. If I could figure out how to cure that error I should be able to see the hours billed in one column and the dates in another column. I am wondering if I am not using the split correctly. Or if I should not have two value types in the same column. Any direction is totally appreciated! Many Thanks.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter

Free Excel Training Webinar Modern Power Tools