Merge sort with alternative sort orders (EBCDIC vs ASCII)

[emarc1]

I thought the source data for one of my current projects was sorted, so I've been using merge sort algos in Power Query (eg. GroupKind.Local and JoinAlgorithm.SortMerge). Much faster! But I've hit some issues that I've realised may be caused by the data being in EBCDIC sort order (where letters are before numbers), rather than like ASCII sort order (numbers first).

As an example, the PQ merge sort algos seem to be fine through item codes like A001, A1234, C001, etc... But then there's an RM123 and the following R001 would get ignored. It shouldn't be a problem, because the merged tables actually have the same source, but perhaps these algos don't look for the next different value in the sorted list, but the next higher value, or something like that (R001 would be lower than RM123 in ASCII).

I've had to sort the data in Power Query to get it to work, for now. Does anyone have any ideas on the most optimal way of grouping and merging data that is sorted in this way?

[emarc1]

Some more details:

Table 1 has stock movements and dates, with the resulting stock balance at the end of that day.

Table 1

Table 1 doesn't have rows on days where there were no stock movements for an item, so it needs to be put into a full calendar and filled down for each item/stockroom group. Table 1 is merged into table 2 which has the same item list, but a full calendar of dates (though it's just set to show a week here):Table 2

At this stage, the groups haven't been filled down yet, but you can see that the RM2490 values have merged over correctly, but the R011 and onwards haven't.

A later stage uses GroupKind.Local in a Table.Group function and seems to group all the rows correctly, despite the EBCDIC sort order. It just seems to be an issue with JoinAlgorithm.SortMerge. With further testing, I see that it doesn't work with descending sort orders either so I think it's limited to purely ASCII ascending sorted columns only.

For now, I think I'm forced to at least do some data sorting in PQ, but at least I think I know the most optimum places to do it. Hopefully I can find a way to switch the sort order at the source later.

[v-stephen-msft]

Hi @emarc1 ,

You may create a custom column or merge with a table which contains your item codes and the corresponding sort number, and then sort it by the number column.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[emarc1]

The order of the items in both tables is the same, because they both come from the original table in SQL. It just seems to be that them not being in ASCII order seems to cause PQ some issues.

Wouldn't I find that creating a custom column, merging, and sorting on that would be just as slow as just having PQ sort the columns anyway?

I'm also seeing if I can get the source data to be outputted with an ASCII sort order but it could be tricky.