This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Filtered. There is also an online forum where you can ask questions and share ideas with other community members.
Hello all and welcome back to the Excel Tip of the Week! This week we have a Developer level post in which we're diving into how to use Power Query - labelled Get and Transform in later Excel versions - to perform a very specific and extremely useful data transformation, namely "unpivoting".
What is unpivoting and how do you do it?
You're probably familiar with PivotTables (and see TOTW #190 for the lowdown if you're not). They can be used to create a 2D spread breakdown of data which is useful for presenting multi-dimensional data. But if you're just trying to do the basic things with data - such as summing up or searching for a specific value - this kind of layout is actually very unhelpful. So what to do if you have data in this format and want to put it back? Enter unpivoting.
We want a simpler layout with a Salesperson column, a City column, and a Sales column. So we bring our data into the Query Editor via the From Table command (under Power Query in earlier versions and under Data => Get and Transform in later ones). The Unpivot option is under Transform; we can either select all the city columns and Unpivot Columns, or (a little easier) select Salesperson and Unpivot Other Columns.
This is our resulting query, ready to be saved and loaded back into the spreadsheet (once we rename the newly created column):
From here we could of course recreate the original layout if we really wanted by using a Pivot!
Unpivoting two dimensions
What if we have multiple dimensions spread across columns that we need to unpick? Well, we can still do this if we're careful. For this example we'll use this table of British Airways Avios reward flight redemption prices, from headforpoints.com.
This is the data as it appears when copy-pasting directly from the website. The class (Europe or World Traveller, World Traveller Plus, Club Europe or Club World, and First) is split over columns, as are the costs when flying at peak vs. off-peak times. But we just want a single column for each of Destination, Class, Time, and Cost. First of all, the two-header-row layout here isn't going to fly, and our life will be easier if we make this into a Table. So a couple of quick edits to the data first and this is what we end up loading into PQ:
Once again we are just going to select the one column - the Destination column - and Unpivot Other Columns:
We can now split the Attribute column at our delimiter - " / ":
Then all we have to do is tidy up our column names and export back into Excel:
Now we have a simple, easy to manipulate, searchable table of all our values!
If you want to try these transformations yourself, both datasets are attached in the file at the bottom of this post.
Previous post - Basic arithmetic formulas TOTW index Next post - Revisiting selecting a scenario