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

Watch Video – Fastest Way to Unpivot Data in Excel

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:

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.

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.
3. Click on the Table icon.
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.
6. With any cell selected in the Excel Table, click on the Data tab.
7. In the Get & Transform data group, click on the ‘From Table/Range’ icon.
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.
10. Click on ‘Unpivot Other Columns’ option. This will instantly unpivot your data.
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.
13. (Optional) Change the name of your query to ‘Sales’.
14. Go to the Home tab (in the query editor).
15. Click on Close and Load.

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.

Also read: Transpose Multiple Rows into One Column

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

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.
4. Right-click on the Sales query in the Queries pane.
5. Click on Refresh.

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:

• Combine Data from Multiple Workbooks in Excel (using Power Query)
• Combine Data From Multiple Worksheets into a Single Worksheet in Excel.
• Get a List of File Names from Folders & Sub-folders (using Power Query).
• Merge Tables in Excel Using Power Query.
• How to Add and Use an Excel Pivot Table Calculated Field.
• How to Refresh Pivot Table in Excel.