如何基于TAB2的MASK匹配TAB1的ACCOUNT实现两表关联？

现有表结构 #

TAB1存储产品及对应账户信息，TAB2存储分组及对应账户掩码信息：

+-------------------+  +-------------------+
|        TAB1       |  |        TAB2       |
+---------+---------+  +---------+---------+
| PRODUCT | ACCOUNT |  |   GR    |   MASK  |
+---------+---------+  +---------+---------+
| apple   |  1001   |  | fruits  |  1%     |
| banana  |  1002   |  | cars    |  _2%    |
| bike    |  2101   |  | bikes   |  21%    |
| car     |  2202   |  | other   |  2[^12] |
| tree    |  2401   |  | spec    |  2%     |
| pool    |  2502   |  +---------+---------+
+---------+---------+  

期望关联结果 #

需生成分组与产品的关联表TABRES，一个产品可对应多个分组（例如spec分组关联cars、bikes、other分组下的所有产品）：

+-----------------------------+
|          TABRES             |
+---------+-------------------+
|   GR    | PRODUCT | ACCOUNT |
+---------+---------+---------+
| fruits  | apple   |  1001   |
| fruits  | banana  |  1002   |
| cars    | car     |  2202   |
| bikes   | bike    |  2101   |
| other   | tree    |  2401   |
| other   | pool    |  2502   |
| spec    | bike    |  2101   |
| spec    | car     |  2202   |
| spec    | tree    |  2401   |
| spec    | pool    |  2502   |
+---------+---------+---------+

问题说明 #

需求是通过TAB2的MASK字段匹配TAB1的ACCOUNT字段，实现分组与产品的关联。用户尝试用PowerQuery的Table.Join做左关联：

= Table.Join(tab2, {"   MASK  "}, tab1, {" ACCOUNT "}, JoinKind.LeftOuter) 

但执行后PRODUCT和ACCOUNT字段全为null，未得到预期结果。用户思路是针对TAB2每一行，用MASK作为匹配条件过滤TAB1，生成临时表后合并所有结果，但不知如何落地。

解决方案 #

在PowerQuery中可通过添加自定义列+展开表+合并结果实现，具体步骤和代码如下：

核心逻辑 #

将TAB2中的SQL风格通配符（%、_）转换为正则表达式，用正则匹配TAB1的ACCOUNT字段，逐行过滤后合并结果。

完整M代码示例 #

假设TAB1和TAB2已加载到PowerQuery中，替换代码中的数据源路径即可使用：

let
    // 加载数据源（根据实际情况替换）
    tab1 = Excel.CurrentWorkbook(){[Name="TAB1"]}[Content],
    tab2 = Excel.CurrentWorkbook(){[Name="TAB2"]}[Content],
    // 为TAB2添加自定义列，存储匹配TAB1的结果
    添加匹配结果 = Table.AddColumn(tab2, "匹配结果", (row) => 
        let
            mask = row[MASK],
            // 将SQL通配符转换为正则表达式：%→.*，_→.，保留[^12]这类原生正则语法
            regexPattern = "^" & Text.Replace(Text.Replace(mask, "%", ".*"), "_", "."),
            // 过滤TAB1中符合正则的行
            filteredRows = Table.SelectRows(tab1, each Text.Matches([ACCOUNT], regexPattern))
        in
            filteredRows),
    // 展开匹配结果表中的产品和账户字段
    展开匹配结果 = Table.ExpandTableColumn(添加匹配结果, "匹配结果", {"PRODUCT", "ACCOUNT"}, {"PRODUCT", "ACCOUNT"}),
    // 调整列顺序与预期结果一致
    调整列顺序 = Table.ReorderColumns(展开匹配结果, {"GR", "PRODUCT", "ACCOUNT"})
in
    调整列顺序

步骤拆解 #

1. 转换通配符：把TAB2的MASK字段从SQL通配符转为正则表达式，确保匹配逻辑生效。
2. 逐行过滤：针对TAB2的每一行分组，用转换后的正则匹配TAB1的账户，筛选出符合条件的产品。
3. 合并结果：展开自定义列中的筛选结果表，整理列顺序后得到最终关联表。

内容的提问来源于stack exchange，提问作者Jacek