Forum Discussion
sort zero length text strig power query v excel
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.
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.
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,
Power Query sorts them one way, Excel sorts them the opposite way. It's just how each engine processes Unicode.
