Solved: Table.Sort --- not sorting correctly

Anonymous · ‎10-16-2020

OK its the end of the week but and a simple sort is defeating me....

So I want to sort a table by two columns and then I will remove duplicate so keeping the top most one of each - works normally but today tried it again on a new data set and while I ask it to sort.Ascending it sorts it decending!

#"Wrangle DATReport2: Sort" = Table.Sort(#"Wrangle DATReport2: Create Sort IDX", {{"CountySRNum", Order.Ascending}, {"Sort_IDX", Order.Ascending}})

I always thourght 2 came before 3 !!

Anyone any ideas....

Thanks

ImkeF · ‎10-16-2020

Hi @Anonymous ,

Table.Sort (and List.Sort) won't keep their order without a Buffer-function around it:
https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390
Don't ask...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Anonymous · ‎10-16-2020

So the work around was to create a temp column with the columns wanted to sort.

But still none the wiser why the Sort method is not giving the expacted results.

Andrew

ImkeF · ‎10-16-2020

Hi @Anonymous ,

Table.Sort (and List.Sort) won't keep their order without a Buffer-function around it:
https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390
Don't ask...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous · ‎10-16-2020

Hi @ImkeF I was just running a test with a Table.Buffer as your message came in and I can confirm it solves the issue. I think i picked this up from one of your articles in the past and then obviously end of the week haze forgot all about it. It is a bit nuts that the command need to be wrapped to make it useful. Thanks again Andrew