Unpivoting Data

Right then – unpivoting.

Unpivoting is the function that takes multiple columns of data and turns them into a single column of data with multiple rows.

Let’s say we have a table of employee data. This table is made up of the employee name in one column and then 4 other columns, one each for ageing bracketing e.g. 18-30, 31-40, 41-50 and 50+. Now if we wanted to do any sort of analysis over the ageing of the employees, say in a pivot table, that wouldn’t be too straightforward as the ageing information is in 4 different columns. You’d need to run analysis over each column. It would be better to convert the four columns into a single column (maybe called “Ageing”) that has a different row for each aging bracketing for each employee. That’s what the unpivoting function does. That’s good news as there’s no way to easily perform that conversion in standard Excel.

How do we perform the Unpivoting function?

Well job one is to connect to our file or files that have the information on which we want to perform the unpivot function. Now of course we know how to do all of that from our previous lessons so we’re going to skip that part and go straight to the fun bit.

1. Find the columns that you’d like to convert into a single column
2. Make sure that the titles of the columns are in the headers – use the ‘Promote First Row to Headers’ function if necessary
3. Highlight the columns that you’d like to convert into a single column
4. Right click any of the highlighted column titles
5. Select Unpivot columns.

Now PowerQuery has removed the highlighted columns and then created a new column with a row for each column title. PowerQuery has also create a second new column containing the data of the highlighted rows.

Really simple and really effective not to mention super easy to analyse.