Power Query (Get & Transform data in Excel 2016) is a must have tool, if you wrangle with data every day. Here is a quick introduction, in case you are new.
Let’s learn how to use Power Query to unpivot data.
Essentially, we are trying to go from left to right in this picture.
Doing something like this thru either formulas or VBA can be very complex. But Power Query can get you unpivoted data in just a few clicks. Sounds interesting? Read on.
Tutorial: Unpivot data using Power Query
Step 1: Set up your pivoted data as a table
If you want Power Query to work with data in Excel, it must be in table form. So select any cell in the pivoted data and press CTRL+T to turn it in to a table.
At this stage, we get this:
Step 2: Load table data in to Power Query
While keeping the selection inside pivot data, go to Power Query ribbon (or Get & Transform area of Excel 2016 data ribbon) and click on “from Table” button.
This will take your table data and load it in to a new query in Power Query. It looks like this:
Step 3: Get rid of grand totals
When unpivoting data, we don’t need the grand totals. To remove them,
- Select the grand total column
- Click on “Remove Columns” button in query editor (Power Query window)
- Click on “Remove Rows” button, select remove bottom rows option.
- Enter the number of rows as 1
At this stage, grand total column & row are gone. We end up with this:
Step 4: Fill down the missing region names
If your pivot table has null / blank values in the first column, you can fill them with values from above cells using the Fill option of query editor. Select the Region column and click on the Fill button from transform ribbon. See this demo:
Step 5: Remove sub-total rows by filtering them away
Click on the filter button next to region and filter away all the sub-total columns too. We don’t need them for unpivoting.
Step 6: Unpivot the data
Now that our data is in correct shape, let’s unpivot.
Select the last 3 columns and click on Unpivot columns button in Transform ribbon.
And we get the unpivoted data.
You can load this data to Excel or to your data model for further processing.
Download example Power Query workbook
Please click here to download the example workbook for this tutorial. To examine the query settings and power query steps,
- Open the workbook
- Go to Power Query ribbon (or Data ribbon in Excel 2016) and click on Workbook Queries Show Pane option.
- Right click on “Unpivot Data” query and choose edit
- This opens the query editor. You can examine the steps in the query steps pane to right.
Learn more about Power Query / Get & Transform data:
Power Query (or less intimidating Get & Transform data in Excel 2016) is an impressive technology to help you deal with common data problems easily. If you are an analyst who relies on Excel, learning Power Query is going to make you super productive. Check out below tutorials to get started with this amazing feature.
- Introduction to Power Query – podcast
- Importing web data using Power Query
- Recommended Power Query training from Ken & Miguel
How do you unpivot your data?
I used to write VBA programs to unpivot my data. But now that I have Power Query, I use it anytime I need unpivoting.
What about you? How do you unpivot your data? Please share your thoughts and tips in the comments section.
9 Responses to “Unpivot data quickly with Power Query [tutorial]”
In Step 3 you could filter out Salesman column, and remove nulls, excluding totals and subtotals in that way.
Thanks, very helpful!
Ummm...what am I missing. 2 easier ways.
Why not just double click the grand total. The detail then jumps into a separate sheet of everything in the original spreadsheet.
A second way is to just place everything that you want into the same rows and columns. Go to Design-Report Layout-Show in Tabular Form and Design-Report Layout-Repeat All Item Labels. Then copy everything to a new spreadsheet, Paste Values and then convert to a table.
This all seems way easier.
Hi Mark,
Of course, if you could double click (or change pivot layout), you then also have access to original data. So no need to go thru this process. The Power Query technique is useful when you just have data in pivot format (for example a report dump) and want to reshape it for some other process.
I agree with Chandoo. I too wondered whole night why somebody would unpivot when the basis of making Pivot is itself based on the table already existing. This illustrates how powerful power query is.
Thanks once again Chandoo!
Yes true Mark,
was my first thought too -
but during my courses I get many people who get data from a datawarehouse and they don't have access to the source data - their question typically is how to analyse the data with sorts/filters and their own pivot tables... so in these scenarios these power query tools are a blessing.
tikè?
Hi
Can I know how to remove subtotals in querry window? I have 92 rows with 'Sub Total' and couldn't do it with power querry. Please help.
Regards
Rudra
Hello Rudra. May be you can filter them out? Just make text filter on your column with condition like "Not Contains 'Sub Total'
Here is how it can looks in M language:
= Table.SelectRows(Source, each not Text.Contains([Region], " Total"))
Hi Chandoo
I have a question about the pivot columns themselves.
Here you have FastCar, RapidZoo, SuperGlue, all in nice alphabetical order. But what if they were SuperGlue, FastCar, RapidZoo and you wanted them in alphabetical order? I guess you could just move the columns.
But what if you bring in, for example based on your screen shot, different countries which now introduces other columns such as MendozaWine, NiceCheese, OpenHat, and SlowMetro. Now how will the column order look?
What can you do to always ensure that the columns come out in the desired order after pivoting, regardless of what columns they are: FastCar, MendozaWine, NiceCheese, OpenHat, RapidZoo, SlowMetro, SuperGlue.
Any help will be appreciated. Thanks and I hope I didn't confuse anyone.