Excel Tip of the Week #425 - Revisiting unpivoting

Finding unique values

Let’s say we have a single column of data with duplicates in it:

We can remove the duplicates and be left with just the unique values by using Data => Remove duplicates, or in Excel 365 by using the UNIQUE function. But what about if we have multiple columns of data – let’s say attendance at a course by day?

Excel’s inbuilt duplicate-removing functions both work on the presumption that you care about unique rows. But what if instead you want a comprehensive list of all the students? To do it with one of the above approaches you would have to first copy and paste until all the data was in one column. But it is much simpler by unpivoting in Power Query.

First, we use Data => From Table/Range to import the data into PQ:

Now we select all 4 columns and use Transform => Unpivot Columns:

Finally, we remove the day of the week column and use right click => Remove duplicates. We can then export our resulting list:

Unpivoting or repivoting a task list

Unpivoting can also be used more literally to take 2D data and condense it into a simpler columnar format. Let’s take this task rota:

The data here is already 'pivoted' – with the 'day of the week' data displayed horizontally and the tasks data vertical, and then the assigned people in the 2D intersectional area between them. This format makes lookups and other kinds of further manipulation a bit trickier, so we might want to use unpivot to get it into a more straightforward columnar layout.

The simplest approach here is to select the Job column and use Unpivot other columns:

That plus a few quick column renamings will get our output sorted.

But there’s another option here that’s really handy – we can repivot this data in a different way! So for example we could pivot the job column and choose 'Don’t aggregate' from the list of operations, and we get this:

And just like that, we have a version with the people listed in columns and the tasks across the rows! And you could use this approach to get any layout you wanted from any other layout.

Check out all these examples in this example file.

• Previous post - Filters redux
• TOTW index
• Next time – Passwords redux!