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.
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.
just to add i did find this ;
Power Query uses binary Unicode ordering, not Excel’s “soft whitespace” rules.
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)