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
<tbody>
</tbody><colgroup><col></colgroup>
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
<tbody>
</tbody><colgroup><col></colgroup>
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.'
For example, I have the following values
Value |
#750 |
#770 |
#8 |
#9 |
000.000.01001 |
000.000.01003 |
000.000.01004 |
000.000.01005 |
000-001 |
000-0030 |
000-0049-00 |
001 |
<tbody>
</tbody><colgroup><col></colgroup>
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
Value |
#750 |
#770 |
#8 |
#9 |
000-001 |
000-0030 |
000-0049-00 |
000.000.01001 |
000.000.01003 |
000.000.01004 |
000.000.01005 |
001 |
<tbody>
</tbody><colgroup><col></colgroup>
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.'