Forum Discussion
Remove duplicates based on sort in Power Query
I have a table that has ID and date columns. I want to remove duplicates and only keep the most recent record for each ID. I thought I'd by able to simply by sorting either ascending or descending then removing duplicates, but it doesn't keep the first or last record in your sort. It only keeps the first record in the source dataset regardless of sort. Is there a way around this?
I've run into this before. I think it's a result of query folding not operating in the order that you'd expect.
The workaround that worked for me was to add an index column after sorting before removing duplicates. This breaks query folding and forces it to operate in the order you just defined by your sort.
AlexisOlson Anonymous
Do check this video from curbal.com. It beautifully explains the reason why changing Sort Order in Power query doesn't work for removing duplicates and using Table.Buffer to keep new Sorting Order in memory.
Sharing this as it might be helpful in some other scenarios too..
12 Replies
I've run into this before. I think it's a result of query folding not operating in the order that you'd expect.
The workaround that worked for me was to add an index column after sorting before removing duplicates. This breaks query folding and forces it to operate in the order you just defined by your sort.
AlexisOlson Anonymous
Do check this video from curbal.com. It beautifully explains the reason why changing Sort Order in Power query doesn't work for removing duplicates and using Table.Buffer to keep new Sorting Order in memory.
Sharing this as it might be helpful in some other scenarios too..
I need to remove duplicates based on keeping the second instance rather than the first, if I add an index and sort descending then remove duplicates should that work?
If there are always exactly two instances, this should work. Are there always at least two and no more than two?
Maybe it is possible to use the function that was added in 2022, Table.StopFolding( SortedTable ), to break folding and keep the sorting.
