Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.


Bug warning for Table.Sort and removing duplicates in Power Query

Should have written this blogpost ages ago, as I've often come across people complaining that removing duplicates doesn't work as expected.


What's going on?


When duplicates are removed, only the first occurances of the values are kept. Therefore people often sort their data as desired before hitting the remove duplicates button. Later on, they see that not the right items are kept.


Sort order will not be kept by default


This is due to the fact, that the sort order in Power Query will not be kept by default (see this thread for more details)


Table.Buffer to the rescue


Solution is to wrap your sort-statement into a Table.Buffer command like so:

Table.Buffer ( Table.Sort ( YourTable, {{ YourSortColumnName, Order.Ascending }} ) )

This will keep the sort order for the next step reliably.


Other functions with unexpected behaviour 


This is something that should have made it into the functionn documentation, but so far hasn't. Therefore I've created an alternative documentation series where I've mentioned "unexpected behaviour" like that:


Do you know other functions that don't behave as expected? Please let me know in the comments or contribute to the GitHub repo.


Enjoy & stay queryious 😉

What is your favorite Power BI feature release for November 2023?