Power Query and Excel don’t agree on sorting?

Take a table that has the following records

Field A
|
a

If I sort it with Excel, I get the pipe characterbefore the “a”

Field A
|
a

Now, if I sort it with Power Query, I get

Field A
a
|

That can be a problem in some situations (for instance, i’m using the double vlookup trick for performance reason, and it gets messed by this discrepancy).

Here’s a screenshot that illustrates the problem (it’s actually wider than what I described above, because they also don’t agree on the sorting of empty cells): screenshot. And here’s a link to this sample workbook.

So my questions are: is that a bug or a “feature”? And most of all, is there a way to make Power Query sort like Excel does?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s