Power Query: Sort it Out

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!