Forum Discussion
Remove duplicates based on sort in Power Query
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'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.
Thanks Alexis! Worked perfectly.
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..
AnkitBI , are there advantages to using Table.Buffer versus using Index like AlexisOlson suggested? Index worked for me, but I wanted to know if there are cases where it won't.
Thanks!
I tried using Table.Buffer today before removing duplicates, and I ended up freezing up my file. But adding Index seemed to perform better.
