Finding unique values
Let’s say we have a single column of data with duplicates in it:
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:
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.
Join the Excel Community
Do you use Excel in your organisation? Are you using it to its maximum potential? Develop your skills and minimise spreadsheet risk with our Excel resources. Membership is open to everyone - non ICAEW members are also welcome to join.
- Excel Tips & Tricks #479 – Advanced Filter redux
- Excel Tips & Tricks #478 - Introduction to Power Automate
- Excel Tips & Tricks #477 - Introduction to creating map charts in Excel
- Excel Tips & Tricks #476 - Cross join in Excel using a formula
- Excel Tips & Tricks #475 - Using ‘Show Changes’: keeping track of cell edits and the power of cloud storage
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.