如何在Power Query M脚本中实现整单元格匹配批量替换字符串

我需要给客户调查的答案添加数字排名，示例如下：

由于答案数量多且随调查变化，我使用了网上找到的如下BulkReplace批量替换M脚本：

let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
    let
        //Convert the FindReplaceTable to a list using the Table.ToRows function
        //so we can reference the list with an index number
        FindReplaceList = Table.ToRows(FindReplaceTable),
        //Count number of rows in the FindReplaceTable to determine
        //how many iterations are needed
        Counter = Table.RowCount(FindReplaceTable),
        //Define a function to iterate over our list 
        //with the Table.ReplaceValue function
        BulkReplaceValues = (DataTableTemp, n) => 
        let 
            //Replace values using nth item in FindReplaceList
            ReplaceTable = Table.ReplaceValue(
                DataTableTemp,
                //replace null with empty string in nth item
                if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
                if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
                Replacer.ReplaceText,
                DataTableColumn
                )
        in
            //if we are not at the end of the FindReplaceList
            //then iterate through Table.ReplaceValue again
            if n = Counter - 1 
                then ReplaceTable
                else @BulkReplaceValues(ReplaceTable, n + 1),
        //Evaluate the sub-function at the first row
        Output = BulkReplaceValues(DataTable, 0)   
    in
        Output
in
    BulkReplace

但该脚本会替换子字符串，例如处理“very often”时，会因“often”被替换而变成“4_very 3_often”或“very 3_often”（取决于替换顺序）。Power Query可视化替换功能有“匹配整个单元格内容”选项可解决此问题，但手动添加50步效率太低，请问如何在M脚本中实现该整单元格匹配的批量替换功能？

解决方案 #

只需修改原脚本中的替换器函数，将Replacer.ReplaceText替换为Replacer.ReplaceValue即可实现整单元格匹配替换。修改后的完整脚本如下：

let BulkReplace = (DataTable as table, FindReplaceTable as table, DataTableColumn as list) =>
    let
        FindReplaceList = Table.ToRows(FindReplaceTable),
        Counter = Table.RowCount(FindReplaceTable),
        BulkReplaceValues = (DataTableTemp, n) => 
        let 
            ReplaceTable = Table.ReplaceValue(
                DataTableTemp,
                if FindReplaceList{n}{0} = null then "" else FindReplaceList{n}{0},
                if FindReplaceList{n}{1} = null then "" else FindReplaceList{n}{1},
                Replacer.ReplaceValue, // 替换为整值匹配的替换器
                DataTableColumn
                )
        in
            if n = Counter - 1 
                then ReplaceTable
                else @BulkReplaceValues(ReplaceTable, n + 1),
        Output = BulkReplaceValues(DataTable, 0)   
    in
        Output
in
    BulkReplace

原理说明 #

• Replacer.ReplaceText：会在单元格文本中查找并替换子字符串，只要文本中包含目标内容就会替换，这是导致"very often"被错误拆分替换的原因。
• Replacer.ReplaceValue：仅当单元格的整个值完全等于目标查找值时才会执行替换，完美对应可视化界面中的“匹配整个单元格内容”选项。

这样修改后，无论替换顺序如何，"very often"只会在查找表中存在完全匹配的条目时才会被替换为"4_very often"，而不会因为包含"often"子串被错误修改。

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