Power BI PowerQuery条件列实现不区分大小写整词精确匹配

问题说明 #

现有表中存在Action文本列，需派生两个标记列：

• Informal：列中存在informal相关表述时填入Y，否则为空
• Formal：列中存在formal相关表述时填入Y，否则为空

原有实现直接使用Text.Contains做子串匹配，存在两个问题：

1. 重复判断大小写分支，代码冗余
2. 单词informal本身包含formal子串，所有含informal的行会被误判为同时命中formal规则，出现两列同时标记Y的错误

原有错误代码如下：

// 旧Informal列逻辑
Table.AddColumn(#"Filtered Rows", "Informal", each if Text.Contains([#"Action"], "informal") then "Y" else if Text.Contains([#"Action"], "Informal") then "Y" else null)

// 旧Formal列逻辑
Table.AddColumn(#"Added Conditional Column1", "Formal", each if Text.Contains([#"Action"], "formal") then "Y" else if Text.Contains([#"Action"], "Formal") then "Y" else null)

兼容全版本实现方案（无需正则、无额外依赖） #

该方案适配所有版本的Excel、Power BI内置PowerQuery环境，无需开启任何预览功能：

1. 给Text.Contains传入第三个参数Comparer.OrdinalIgnoreCase，一次性实现不区分大小写匹配，替代原有重复判断大小写的冗余写法
2. 匹配Formal规则前，先把原文本中所有不区分大小写的informal字符串移除，从根源上消除子串干扰

直接替换原有添加列步骤的代码如下：

Table.AddColumn(
    Table.AddColumn(#"Filtered Rows", "Informal", each 
        if Text.Contains([Action], "informal", Comparer.OrdinalIgnoreCase) then "Y" else null
    ), 
    "Formal", each 
        let
            // 清除所有informal匹配项，排除子串误判
            CleanedActionText = Text.RemoveMatchingText([Action], "informal", Comparer.OrdinalIgnoreCase)
        in
            if Text.Contains(CleanedActionText, "formal", Comparer.OrdinalIgnoreCase) then "Y" else null
)

严格整词匹配方案（适配新版PowerQuery环境） #

如果使用新版Power BI、2021及以上版本Excel的PowerQuery，可通过正则单词边界符\b实现严格整词匹配，后续出现其他包含formal的组合词（比如informal、nonformal）也不会出现误判，代码如下：

// 步骤开头先定义整词匹配工具函数
IsWholeWordMatch = (sourceText as text, keyword as text) as logical =>
    let
        // 不区分大小写匹配独立单词
        HtmlPayload = "<script>document.write(/\b" & keyword & "\b/i.test('" & Text.Replace(sourceText, "'", "\'") & "') ? 'Y' : 'N')</script>",
        MatchResult = Web.Page(HtmlPayload)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
    in
        MatchResult = "Y",
// 批量添加两个标记列
ResultTable = Table.AddColumn(
    Table.AddColumn(#"Filtered Rows", "Informal", each if IsWholeWordMatch([Action], "informal") then "Y" else null),
    "Formal", each if IsWholeWordMatch([Action], "formal") then "Y" else null
)
in
ResultTable

匹配效果参考 #

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