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) 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):
Here are the steps to refresh the already created query and unpivot this data: 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.
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.
