Power Query中基于三列匹配的规则校验优化需求

问题核心 #

你需要在百万行数据下完成两个表的规则校验，避免复制表带来的性能损耗，原方案因多次遍历列导致性能极差。规则逻辑：

• 对Table2每一行，先检查(PSET.rule, Parameter.rule, Attribute)组合是否存在于Table1
• 若存在，进一步验证Table1中对应(PSET, Parameter)的Attribute是否等于Table2的AttributeRight：满足返回true，仅存在组合返回false，否则返回null

优化思路 #

放弃原方案中逐行遍历整列的低效方式，改用字典（Dictionary）实现O(1)快速查找，仅需两次遍历Table1构建辅助结构，再一次遍历Table2完成校验，整体时间复杂度从O(n*m)降至O(n+m)，彻底解决性能问题。

实现代码 #

let
    // 1. 预处理Table1，构建两个字典用于快速查询
    // 字典1：存储Table1中所有(PSET, Parameter, Attribute)组合，键为拼接字符串，值为true
    Table1_Combination_Dict = Table.ToDictionary(Table1, 
        each [Key = [PSET] & "|" & [Parameter] & "|" & [Attribute], Value = true], 
        (k) => k.Key
    ),
    // 字典2：存储Table1中(PSET, Parameter)与Attribute的映射，键为拼接字符串，值为对应Attribute
    Table1_PSET_Param_Map = Table.ToDictionary(Table1, 
        each [Key = [PSET] & "|" & [Parameter], Value = [Attribute]], 
        (k) => k.Key
    ),

    // 2. 给Table2添加校验列
    Table2_With_Verification = Table.AddColumn(Table2, "Verifica regola", (currentRow) =>
        let
            // 构建当前行的组合查询键
            comboKey = currentRow[PSET.rule] & "|" & currentRow[Parameter.rule] & "|" & currentRow[Attribute],
            // 检查组合是否存在于Table1
            isComboExist = Dictionary.ContainsKey(Table1_Combination_Dict, comboKey),
            // 若存在，验证Attribute是否匹配
            isAttributeMatch = if isComboExist then 
                                Table1_PSET_Param_Map[currentRow[PSET.rule] & "|" & currentRow[Parameter.rule]] = currentRow[AttributeRight]
                              else false
        in
            if isComboExist then isAttributeMatch else null
    )
in
    Table2_With_Verification

注意事项 #

• 如果Table1中存在重复的(PSET, Parameter)组合，需先去重，否则Table.ToDictionary会因键重复报错：

  Table1_Clean = Table.Distinct(Table1, {"PSET", "Parameter"})
  

  然后用Table1_Clean替换代码中的Table1即可。
• 拼接键时使用的分隔符（|）需确保不会出现在PSET/Parameter/Attribute的实际值中，避免键冲突，可根据数据情况更换为其他字符。

原方案问题说明 #

1. 逻辑错误：原代码中三个List.Contains分别检查单列值存在，未验证三个值是否属于Table1的同一行，会导致错误匹配。
2. 性能极差：每一行都要三次遍历Table1的整列，百万行下会产生数十亿次操作，完全无法高效运行。

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