Please note javascript is required for full website functionality.
MVP

Blog

Power Query: Sort it Out

29 January 2020

Welcome to our Power Query blog.  This week, I look at sorting issues.

 

I have a very simple query:

I also have a simple goal: to sort my data by salesperson, and then show the latest visit date for each salesperson.

I begin by sorting by salesperson, which I can do using the arrow icon at the top of the Salesperson column.

I choose to sort in ascending order.

Next, I sort the visit date in descending order:

I now have my data in order, so I can remove duplicate names by right-clicking on the Salesperson column.

I choose to ‘Remove Duplicates’.

I have one row for each Salesperson, but I don’t have the latest date for all the salespeople.  The sort on date has not been preserved.  In order to keep the order, I need an extra step before I remove duplicates: I need to add an index.

On the ‘Add Column’ tab, I can add an ‘Index Column’.  In this case, it doesn’t matter where I start the index; I choose to start at zero (0).

My data now has an index, so I try removing duplicate names again.

This time I do have the latest date for each salesperson.  When sorting on multiple columns, I can use an index column to preserve the order on my data whilst carrying out transformations on my data.

 

Come back next time for more ways to use Power Query!

Newsletter