Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
7 months ago
Solved

sort zero length text strig power query v excel

  Hello i wanted a custom sort  so   sort    N, S, E, W  order    by adding zero length text ,  but i have found the order seems to get reversed, if done purely in excel  sorting is shortest to lon...
Need Help
Show and Tell
  • ronrsnfld's avatar
    ronrsnfld
    7 months ago

    They are different programs with different sorting algorithms. As to why they were designed differently with regard to zero-width characters, you would have to discuss that with the teams that developed the two different programs. There are other parameters that are also sorted differently by those two programs.

ronrsnfld's avatar
ronrsnfld
Super User
7 months ago

You can do this within the Table.Sort function without adding an extra column:

let
  Source = #table(type table [Txt = text], {{"S"}, {"N"}, {"W"}, {"E"}}),
  order = {"N","S","E","W"},
  #"Sorted Txt" = Table.Sort(Source,  each List.PositionOf(order, [Txt]))
in
    #"Sorted Txt"

 

Results from your data: