Unpivoting Data

Unpivoting Data

Previous Session Summary

In the previous lesson, we looked at how to refresh a folder of information to pick up changes to the information inside the folder or inside the files of a folder. The same technique also works with refreshing a connection to a file rather than a folder. Knowing this technique is absolutely brilliant because suddenly you can:
– Clean and transform multiple files of the same type at once
– Clean and transform new files as they arrive, with no extra effort

Lesson Synopsis

In this lesson we’re going to introduce the concept of unpivoting. This function, after the ability to connect to a folder of files, is one of the most powerful standalone functions in PowerQuery (so go away, make a brew, and return ready to grasp the subject with 100% brainpower).
 
Unpivoting is the concept of turning multiple columns of data into a single column of data. Pivoting of course does the reverse, taking a single column of data and separating it into a number of columns based on the values in that single column.
 
In this lesson we go about looking at how the unpivot function works, converting a table of data into a single column which we can then use for merging (aka LOOKUPs) in the next lesson.

Resource Listing

The Video

Play Video

Lesson Notes

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.

.... and a little more!

Unpivoting pivot tables – Unpivoting can be a really useful function for turning pivot tables into single columns of data. This is part of a more complicated technique that we consider at the advanced PowerQuery stage.