Power Query Sort vs. Excel Sort

[cr731]

I have a column containing numbers and text together that I want to sort in Power Query. I want the sort to mimic the sort order that Excel would use if I sorted and chose "Sort numbers and numbers stored as text separately."

For example, I have the following values
[TABLE="class: grid, width: 93"]
<tbody>[TR]
[TD]Value[/TD]
[/TR]
[TR]
[TD]#750[/TD]
[/TR]
[TR]
[TD]#770[/TD]
[/TR]
[TR]
[TD]#8[/TD]
[/TR]
[TR]
[TD]#9[/TD]
[/TR]
[TR]
[TD]000.000.01001[/TD]
[/TR]
[TR]
[TD]000.000.01003[/TD]
[/TR]
[TR]
[TD]000.000.01004[/TD]
[/TR]
[TR]
[TD]000.000.01005[/TD]
[/TR]
[TR]
[TD]000-001[/TD]
[/TR]
[TR]
[TD]000-0030[/TD]
[/TR]
[TR]
[TD]000-0049-00[/TD]
[/TR]
[TR]
[TD]001[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]


Note that the 001 at the bottom is a text value, not number.

When sorted in Excel, the list sorts as shown above. But if I load this table to PQ, sort, and load back to the worksheet, I get this
[TABLE="class: grid, width: 93"]
<tbody>[TR]
[TD]Value[/TD]
[/TR]
[TR]
[TD]#750[/TD]
[/TR]
[TR]
[TD]#770[/TD]
[/TR]
[TR]
[TD]#8[/TD]
[/TR]
[TR]
[TD]#9[/TD]
[/TR]
[TR]
[TD]000-001[/TD]
[/TR]
[TR]
[TD]000-0030[/TD]
[/TR]
[TR]
[TD]000-0049-00[/TD]
[/TR]
[TR]
[TD]000.000.01001[/TD]
[/TR]
[TR]
[TD]000.000.01003[/TD]
[/TR]
[TR]
[TD]000.000.01004[/TD]
[/TR]
[TR]
[TD]000.000.01005[/TD]
[/TR]
[TR]
[TD]001[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]


The records in red have moved from the PQ sort.

I need the sort to be consistent with Excel's sort so I can use a MATCH formula with the sort criteria as being '1.'

 

[Matt Allington]

I guess you could duplicate the column in PQ, do some type conversions to sort the numbers from the text, and then go from there.

I need the sort to be consistent with Excel's sort so I can use a MATCH formula with the sort criteria as being '1.'

Maybe there is another way. I do t understand what you are saying above. What are you trying to do with match? Can you do it in PQ instead?

 

[cr731]

The PQ table gets loaded back to an Excel worksheet, and I am doing lookups to that table in various worksheets with INDEX-MATCH. Since the table is rather large (about 750k rows), I find that the only way to make the lookup work well is by sorting it and using MATCH where the match type is 1.

The main reason I can't do it all in PQ is that the lookups need to be in various places throughout the workbook and just wouldn't be feasible to load them all into PQ.

What I do that works but adds an extra step is sorting the table after it gets loaded back to the worksheet, so that the sort is done by Excel instead of PQ.

 

[Matt Allington]

It sounds like there are 2 things then. One is the need to have the tables spread throughout the workbook, and the other is the need to do the lookups. But these things can be handled separately. You can load data into power query and not load those tables back to Excel, but just use them in Power Query to do the matching prior to loading this one table.