How to Unpivot Data in Excel using Power Query (aka Get & Transform)

Pivot Tables are great when you want to analyze a huge amount of data in seconds. It also allows you to quickly create different views of data by simply dragging and dropping.

And to create a Pivot Table, you need to have the data in a specific Pivot Table ready format.

In many cases, you're likely to get the data in formats that are not Pivot Table ready.

This often is the case when someone manually collects data and creates a format that is more readable by humans (not Pivot Tables).

Something as shown below:

Unpivot Data in Excel Using Power Query - Data Set

The above data format is something you expect to get as an output of a Pivot Table analysis.

Now, what if you want to analyze this same data, and see what were the total sales by each region or by each month.

While this can easily be done using Pivot Tables, unfortunately, you can't feed the above data into a Pivot Table.

So you need to unpivot data and make it Pivot Table friendly.

Result when you Unpivot Data using Power Query transformation

While there are some ways to do this using Excel formula or VBA, Power Query (Get & Transform in Excel 2016) is the best tool to unpivot data.

Unpivot Data Using Power Query

Here are the steps to unpivot data using Power Query:

(If your data is already in an Excel Table, start from step 6 onwards)

  1. Select any cell in the dataset.
  2. Go to the Insert Tab.Insert Tab in the Ribbon
  3. Click on the Table icon.Click on the Table Icon to create an Excel table
  4. In the ‘Create Table' dialog box, make sure the range is correct. You can modify the range if needed.
  5. Click OK. This will convert your tabular data into an Excel table.Create Table dialog box in Excel
  6. With any cell selected in the Excel Table, click on the Data tab.Data tab in the ribbon
  7. In the Get & Transform data group, click on the ‘From Table/Range' icon.click on from table range to create a power query to unpivot data
  8. In the Create Table dialog box that opens (if it opens), click on OK. This will open the Query Editor using the Excel Table data.
  9. In the Query editor, right-click on the Region column.Right click on Region column Power Query to Unpivot Data
  10. Click on ‘Unpivot Other Columns' option. This will instantly unpivot your data.Click on Unpivot Other Columns
  11. Change the name of the ‘Attribute' column to a more meaningful name, such as ‘Months'.
  12. Once you have the Unpivoted data, it's a good practice to make sure the data types are all correct. In this example, click on one cell for each column and see the data type in the Transform tab. If needed, you can change the data type as well.Data Type in the Transform tab
  13. (Optional) Change the name of your query to ‘Sales'.Change the name of the query
  14. Go to the Home tab (in the query editor).Home tab in Power Query Editor
  15. Click on Close and Load.Close and Load Power Query - Load Unpivot Data in Excel

The above steps would unpivot your data set using Power Query and put in back in Excel as a Table in a new worksheet.

Now you can use this data to create different views using a Pivot table. For example, you can check the total sale value by month or by region.

Refreshing the Query When New Data is Added

This all works fine.

But what happens when new data is added to our original data set.

Let's say you get data for July which is in the same format as the one with which we started.

Do I need to repeat all the steps again to include this data in my unpivoted dataset?

The answer is NO.

And that is what is so awesome about Power Query. You can continue to add new data (or modify existing data), and Power Query would update it instantly as soon as you refresh it.

Let me show you how.

Suppose below is the new dataset that I get (which has additional data for July):

July Data to Unpivot in Excel

Here are the steps to refresh the already created query and unpivot this data:

  1. Add this new data to your original data that you used to create the query.
  2. Since you're adding data to the adjacent column of an Excel table, the Excel Table will expand to include this data in it. If it doesn't by any chance, do it manually by dragging the small inverted ‘L' icon at the bottom-right of the Excel Table.
  3.  Go to the Data tab and click on Queries & Connections. This will show a pane with all the existing queries in it.Queries and Connections in the Ribbon
  4. Right-click on the Sales query in the Queries pane.Queries Pane that has all the queries at one place
  5. Click on Refresh.Refresh Power Query to Unpivot Data in Excel

That's it! Your new data is instantly unpivoted and added to the existing data.

You would notice that the number of rows shown in the Query updates to show you the new numbers. In this example, it was 24 before the refresh and became 28 after the refresh.

This also means that if you have created any Pivot Tables using the data you got from Power Query, those Pivot Tables would also refresh to show you the updated results.

You May Also Like the Following Excel Tutorials:

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)