Unpivot data using Excel Power Query tools

Here is an example of how you can use Power Query tools to ‘unpivot’ columns of data to make them easier to report from. This is the kind of thing that would have been quite time-consuming to do in Excel before the ‘Power BI’ era, either by manually repeating steps or having to spend time writing and testing a piece of VBA code to perform those steps. Fortunately, it’s now been made very easy using the built-in Power Query functions.

 

Power Query is available as an add-in for Excel 2010 and 2013, and once added it works much the same as the below example. But the example is taking from Excel 2016, with Power Query now built-in and referred to as ‘Get & Transform’.

 

You start with some data of Golf major champions by tournament by year over the past decade. Each year has its own column, but you want to merge them into one so you can report on overall performance from the 10 years.

 

UnpivotColumns1

 

Firstly, click Ctrl + T within the data to turn it into a table, ensuring the full range of cells have been included.

 

UnpivotColumns2

 

Go to the Data tab, and within Get & Transform select New Query. Choose ‘From Table’ and the below screen will appear:

 

UnpivotColumns3

 

Highlight all the year columns by selecting the first column heading, holding down Shift and selecting the last one. Go to the Transform tab and within the Any Column section, Unpivot Columns.

 

 

UnpivotColumns4

 

Click to Save and it outputs into your Excel workbook in this format:

 

UnpivotColumns5

 

Now the data is cleansed and organised in an easy format for data analysis, you can quickly turn it into something informative e.g. this pivot table sorting players by number of majors won in the last 10 years.

 

UnpivotColumns6

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *