Please note javascript is required for full website functionality.


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!