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

[ImkeF]

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: https://github.com/ImkeF/M-Guide

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 😉

[Anonymous]

Hi, colleagues,

I have a similar problem:

The initial code in the Advanced query is:

 #"Added Index" = Table.AddIndexColumn(#"Renamed Columns6", "Index", 1, 1, Int64.Type),

How to fix it, I tried:

#"Added Index" = Table.Buffer (Table.AddIndexColumn(#"Renamed Columns6", "Index", 1, 1, Int64.Type)),

Then it refreshed and showed error in this line:

#"Duplicated Column" = Table.DuplicateColumn(Source, "DepositClass", "DepositClass - Copy"),

But it is correct.

May be you have an idea how to fix it?

Thanks

[ImkeF]

Hi @Anonymous ,
this doesn't look like an issue with the Table.Buffer function to me.
The line in which the error is shown (your last line of code) refers to the query "Source" which might contain the problem.
The #Added Index"-query that contains the buffer isn't referenced by the line in question.
If you cannot find it out, I'd recommend that you create a post in the support forum with the full M-code for folks to properly look into it.
/Imke

[Anonymous]

@ImkeF Thank yo for your reply, will do accordingly.

[ValeriaBreve]

Thank you so much! I have been scratching my head so long to try and solve this... until i found your post! I hope that Microsoft will intervene with a hard solution as well in due time.