Power BI - M query to join records matching range

Question

I have an import query (table a) and an imported Excel file (table b) with records I am trying to match it up with.

I am looking for a method to replicate this type of SQL in M:

SELECT a.loc_id, a.other_data, b.stk
FROM a INNER JOIN b on a.loc_id BETWEEN b.from_loc AND b.to_loc

Table A

| loc_id   | other data |
-------------------------
| 34A032B1 | ...        |
| 34A3Z011 | ...        |
| 3DD23A41 | ...        |

Table B

| stk    | from_loc | to_loc   |
--------------------------------
| STKA01 | 34A01    | 34A30ZZZ |
| STKA02 | 34A31    | 34A50ZZZ |
| ...    | ...      | ...      |

Goal

| loc_id   | other data | stk    |
----------------------------------
| 34A032B1 | ...        | STKA01 |
| 34A3Z011 | ...        | STKA02 |
| 3DD23A41 | ...        | STKD01 |

All of the other queries I can find along these lines use numbers, dates, or times in the BETWEEN clause, and seem to work by exploding the (from, to) range into all possible values and then filtering out the extra rows. However I need to use string comparisons, and exploding those into all possible values would be unfeasable.

Between all the various solutions I could find, the closest I've come is to add a custom column on table a:

Table.SelectRows(
     table_b,
     (a) => Value.Compare([loc_id], table_b[from_loc]) = 1
     and Value.Compare([loc_id], table_b[to_loc]) = -1
)

This does return all the columns from table_b, however, when expanding the column, the values are all null.

Answer 1

This is not very specific "After 34A01 could be any string..." in trying to figure out how your series progresses.

But maybe you can just test for how a value "sorts" using the native sorting function in PQ.

add custom column with table.Select Rows:

= try Table.SelectRows(TableB, (t)=> t[from_loc]<=[loc_id] and t[to_loc] >= [loc_id])[stk]{0} otherwise null

To reproduce with your examples:

let
    TableB=Table.FromColumns(
        {{"STKA01","STKA02"},
        {"34A01","34A31"},
        {"34A30ZZZ","34A50ZZZ"}},
        type table[stk=text,from_loc=text,to_loc=text]),

    TableA=Table.FromColumns(
        {{"34A032B1","34A3Z011","3DD23A41"},
        {"...","...","..."}},
        type table[loc_id=text, other data=text]),

//determine where it sorts and return the stk
    #"Added Custom" = Table.AddColumn(#"TableA", "stk", each 
        try Table.SelectRows(TableB, (t)=> t[from_loc]<=[loc_id] and t[to_loc] >= [loc_id])[stk]{0} otherwise null)
in
    #"Added Custom"

Note: if the above algorithm is too slow, there may be faster methods of obtaining these results