如何在Power BI M语言中复现基于字符串范围匹配的SQL内连接逻辑

Let's tackle this—your goal to replicate that SQL BETWEEN logic for string values is totally doable, and your initial attempt was almost right. The issue with your code returning nulls when expanding is that you were referencing entire columns from Table B instead of each individual row's from_loc and to_loc values.

The Core Problem with Your Original Code #

Your custom column code used table_b[from_loc] and table_b[to_loc], which refers to the full columns in Table B, not the specific values from each row you're checking against Table A's loc_id. That's why the filter wasn't working as expected.

Step-by-Step Fix #

Here's how to get the exact result you want, matching your SQL logic:

1. Start with your loaded Table A and Table B in Power Query.
2. Add a custom column to Table A that filters Table B to find rows where the loc_id falls within the from_loc/to_loc range.
3. Expand the filtered results to pull in the stk value.

Correct M Code for the Custom Column

Table.AddColumn(
    TableA, // Replace with your actual Table A name
    "MatchingStockRows",
    (currentARow) => Table.SelectRows(
        TableB, // Replace with your actual Table B name
        (currentBRow) => 
            // Check if loc_id is >= from_loc AND <= to_loc
            Value.Compare(currentARow[loc_id], currentBRow[from_loc]) >= 0 
            and Value.Compare(currentARow[loc_id], currentBRow[to_loc]) <= 0
    )
)

What This Does:

• currentARow represents each single row in Table A as we iterate through it.
• For each currentARow, we scan every row in Table B (currentBRow) and check if the loc_id fits between that row's from_loc and to_loc.
• Value.Compare returns:
  • -1 if the first value is less than the second
  • 0 if they're equal
  • 1 if the first is greater than the second
    So using >=0 and <=0 mimics the SQL BETWEEN behavior (inclusive of the range endpoints).

Finishing Up:

Once you've added this custom column:

• Click the expand icon (the little double arrow) on the MatchingStockRows column.
• Select only the stk column to expand (uncheck "Use original column name as prefix" to keep it clean).
• You'll end up with your desired table, matching the SQL output exactly.

Bonus: Single Match Optimization #

If you know each loc_id will only ever match one range in Table B, you can simplify this to directly add the stk column without needing to expand. This is cleaner and avoids dealing with nested tables:

Table.AddColumn(
    TableA,
    "stk",
    (currentARow) => 
        let
            // Find all matching rows in Table B
            matches = Table.SelectRows(
                TableB,
                (currentBRow) => 
                    Value.Compare(currentARow[loc_id], currentBRow[from_loc]) >= 0 
                    and Value.Compare(currentARow[loc_id], currentBRow[to_loc]) <= 0
            ),
            // Grab the first match (or null if no match)
            firstMatch = Table.First(matches)
        in
            if firstMatch = null then null else firstMatch[stk]
)

Quick Note on String Sorting #

Power Query's Value.Compare uses Unicode sorting order, which works perfectly for your alphanumeric loc_id values (like 34A032B1 vs 34A01). Just make sure your from_loc and to_loc ranges are defined to align with this order (which they are in your example, since 34A01 comes before 34A30ZZZ).

内容的提问来源于stack exchange，提问作者Aaron H.