基于CHANGES表规则批量转换MAIN表列值的Power Query问题

需求概述 #

针对MAIN表的指定列，根据CHANGES表的规则进行修改：

• CHANGES每一行定义规则：varnm是要修改的列，cndvr是判断条件的列，cndvl是条件值的逗号分隔列表
• 若当前行cndvr列的值存在于cndvl列表中，varnm列保持原值；否则将varnm列设为9999

示例数据 #

MAIN表（数值型列）：

ID  | col1  | col2 | cola  | colb
1   |  1    |  17  |   3   |  10
2   |  3    |  6   |   5   |   5

CHANGES表（文本型列）：

varnm | cndvr| cndvl 
 col1 | col2 | 16,17,18,19   
 cola | colb | 5  

预期结果 #

ID  | col1  | col2 | cola  | colb
1   |  1    |  17  | 9999  |  10
2   | 9999  |  6   |   5   |   5

原代码问题排查 #

你编写的REPFUNC函数存在3个核心错误，导致没有任何变化：

1. 条件判断逻辑错误：Text.From(cndvr)取的是列名的字符串（如"col2"），而非当前行cndvr列的实际数值（如17、6），永远无法匹配cndvl中的数值文本
2. 替换值错误：then varnm返回的是列名字符串（如"col1"），而非当前行varnm列的原值，类型不匹配导致替换失效
3. 替换目标错误：each varnm将列名作为要替换的目标值，而非针对varnm列的所有值进行条件替换

修正后的解决方案 #

方案1：修正REPFUNC函数 #

修改函数逻辑，正确引用当前行的列值进行判断：

(row as record, dt as table) as table =>
let
    varnm = Record.Field(row, "varnm"),
    cndvr = Record.Field(row, "cndvr"),
    cndvl = Text.Split(Record.Field(row, "cndvl"), ","),
    // 针对varnm列进行逐行条件替换
    TransformedData = Table.ReplaceValue(
        dt,
        each Record.Field(_, varnm), // 取当前行varnm列的原值
        (currentRow, oldValue) => 
            let
                cndValue = Text.From(Record.Field(currentRow, cndvr)) // 取当前行cndvr列的值并转文本
            in
                if List.Contains(cndvl, cndValue) then oldValue else 9999,
        Replacer.ReplaceValue,
        {varnm} // 指定要替换的列
    )
in
    TransformedData

MAIN查询调用方式不变：

let
    Source = Table.FromRows(
        {{1, 1, 17, 3, 10},{2, 3, 6, 5, 5}},
        {"ID", "col1", "col2", "cola", "colb"}),
    ProcessedData = List.Accumulate(
        Table.ToRecords(CHANGES),
        Source,
        (state, currentRow) => REPFUNC(currentRow, state)
    )
in
    ProcessedData

方案2：无需单独函数，直接整合逻辑 #

可以把函数逻辑直接写到List.Accumulate里，减少冗余：

let
    Source = Table.FromRows(
        {{1, 1, 17, 3, 10},{2, 3, 6, 5, 5}},
        {"ID", "col1", "col2", "cola", "colb"}),
    ChangesRecords = Table.ToRecords(CHANGES),
    ProcessedData = List.Accumulate(
        ChangesRecords,
        Source,
        (currentTable, rule) =>
            let
                varnm = rule[varnm],
                cndvr = rule[cndvr],
                cndvl = Text.Split(rule[cndvl], ","),
                UpdatedTable = Table.ReplaceValue(
                    currentTable,
                    each Record.Field(_, varnm),
                    (row, oldVal) => 
                        if List.Contains(cndvl, Text.From(Record.Field(row, cndvr))) 
                        then oldVal 
                        else 9999,
                    Replacer.ReplaceValue,
                    {varnm}
                )
            in
                UpdatedTable
    )
in
    ProcessedData

验证结果 #

执行上述代码后，MAIN表将输出符合预期的结果：

ID  | col1  | col2 | cola  | colb
1   |  1    |  17  | 9999  |  10
2   | 9999  |  6   |   5   |   5

内容的提问来源于stack exchange，提问作者eli-k