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!