sort zero length text strig power query v excel

[Dicken]

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 longest  and 
this is true 

let
  Source = #table(type table [Txt = text], {{"S"}, {"N"}, {"W"}, {"E"}}),
  order = {"N", "S", "E", "W"},
  #"Added Custom" = Table.AddColumn(
    Source,
    "NewOrder",
    each Text.Repeat(Character.FromNumber(8203), List.PositionOf(order, [Txt]) + 1) & [Txt]
  ),
  textlength = Table.AddColumn(#"Added Custom", "Custom", each Text.Length([NewOrder]))
in
  textlength

now if you sort the neworder A-Z    then you get  N, S, E, W,  but if I load to excel unsorted, then sort in excel  to get 
N,S,E,W  i need to sort Z-A  ?  can someone explain this behaviour ? 

Richard

[ronrsnfld]

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:

[cengizhanarslan]

Add an explicit numeric sort key and sort by that:

#"Added SortKey" =
    Table.AddColumn(
        Source,
        "SortKey",
        each List.PositionOf(order, [Txt]),
        Int64.Type
    )

Then:

• Sort by SortKey

• Display Txt

• Works identically in Power Query, Excel, Power BI, SQL, etc.

[DataVitalizer]

Hi @Dicken 

Excel and Power Query handle zero-width characters differently.

Power Query sorts them one way, Excel sorts them the opposite way. It's just how each engine processes Unicode.

In order to fix it use a number column instead:

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

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Let's connect on LinkedIn

[Dicken]

i was not interested in a fix i the question was why does it treat them differently, 
especially as i as i said you do the whole thing in excel it sort   longest to shortes ie a -z,  , 
power query also sorts a-z ,   i know im' repeatign mysielf, but if you load the power query to excel, 
it then needs to be sorted z -a ,  why does this happent? 

[DataVitalizer]

Power Query sorts them one way, Excel sorts them the opposite way. It's just how each engine processes Unicode.

[ronrsnfld · Accepted solution]

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.

[Dicken]

Thanks, also, i've been doing  a bit of digging for info, also  been  playing around, loading from xl, and pq, 
into pivots, these seem to behave like power query ?  is this correct, 

[ronrsnfld]

I haven't done that type of comparison.

Do you have a use case you need to resolve? Or is this an exploration of the different sorting algorithms being used?

I would expect that Excel and Excel generated pivot tables would sort the same (but, as I wrote, I've never tested it). I don't know about pivot tables generated through Power Pivot, though.

[Dicken]

just to add i did find this ;

Power Query uses binary Unicode ordering, not Excel’s “soft whitespace” rules.

[ronrsnfld]

That is the default for Power Query, but you can use the optional `comparisonCriteria` argument to modify it in many ways (one way is as I showed in my work-around, but it is pretty flexible)

[Dicken]

No, it's not a problem, I just thought it interesting in how they each treated, zero text  as at 
first i thought i was doing something different to cause it but it but i wasn't.