Forum Discussion
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 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
textlengthnow 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
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.
11 Replies
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 💡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?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.
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.
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:
