Power Query中Text.Contains模糊匹配功能的需求及实现方案

Power Query内置的Text.Contains仅支持精确匹配，无法处理拼写误差、近似子串等模糊场景。以下是几种实用的实现方案：

方案1：自定义Levenshtein距离匹配函数 #

Levenshtein距离用于衡量两个字符串的编辑成本（插入、删除、替换字符的次数），通过设定阈值可实现模糊包含判断。

首先定义计算Levenshtein距离的核心函数：

levenshtein = (text1 as text, text2 as text) as number =>
    let
        len1 = Text.Length(text1),
        len2 = Text.Length(text2),
        matrix = List.Generate(
            () => [i=0, j=0, value=0],
            each [i] <= len1,
            each if [j] = len2 then [i=[i]+1, j=0, value=[i]+1]
                 else if [i] = 0 then [i=0, j=[j]+1, value=[j]+1]
                 else let
                         cost = if Text.Range(text1, [i]-1, 1) = Text.Range(text2, [j]-1, 1) then 0 else 1,
                         minVal = List.Min({
                             matrix{[i]-1}{[j]} + 1,
                             matrix{[i]}{[j]-1} + 1,
                             matrix{[i]-1}{[j]-1} + cost
                         })
                     in [i=[i], j=[j]+1, value=minVal],
            each [value]
        ),
        distance = List.Last(matrix)
    in
        distance

再封装成模糊包含函数，支持自定义最大允许编辑距离：

Text.FuzzyContains = (sourceText as text, searchText as text, optional maxDistance as number) as logical =>
    let
        threshold = if maxDistance = null then 2 else maxDistance,
        distance = levenshtein(sourceText, searchText)
    in
        distance <= threshold

使用示例：

Text.FuzzyContains("apple pie", "appel", 1) // 返回true，编辑距离为1
Text.FuzzyContains("hello world", "hola", 2) // 返回false，编辑距离超过阈值

方案2：使用内置模糊查找功能 #

Power Query的Table.FuzzyNestedJoin函数支持批量模糊匹配，可用于判断文本是否包含近似内容：

let
    Source = Table.FromRecords({
        [ID=1, Content="Power Query 入门教程"],
        [ID=2, Content="Excel 高级函数应用"],
        [ID=3, Content="Power BI 数据可视化"]
    }),
    SearchTable = Table.FromRecords({[SearchText="power qu"]}),
    FuzzyJoin = Table.FuzzyNestedJoin(
        Source, {"Content"},
        SearchTable, {"SearchText"},
        "Match",
        [IgnoreCase=true, IgnoreSpace=true, Threshold=0.7]
    ),
    AddFuzzyContains = Table.AddColumn(FuzzyJoin, "IsFuzzyContained", each not List.IsEmpty([Match]))
in
    AddFuzzyContains

参数说明：

• Threshold：0-1之间，值越接近1匹配越严格
• IgnoreCase：忽略大小写差异
• IgnoreSpace：忽略空格差异

方案3：基于子串的近似匹配 #

针对简单拼写错误场景，可生成搜索文本的变体，再检查是否存在于目标文本中：

Text.ApproxContains = (sourceText as text, searchText as text) as logical =>
    let
        lowerSource = Text.Lower(sourceText),
        lowerSearch = Text.Lower(searchText),
        generateVariants = (text as text) as list =>
            let
                len = Text.Length(text),
                deletions = if len > 0 then List.Transform({0..len-1}, each Text.RemoveRange(text, _, 1)) else {},
                insertions = List.Transform({0..len}, each List.Transform({"a".."z"}, (c) => Text.Insert(text, _, c))),
                substitutions = if len > 0 then List.Transform({0..len-1}, each List.Transform({"a".."z"}, (c) => Text.ReplaceRange(text, _, 1, c))) else {},
                allVariants = List.Distinct(List.Combine({deletions, List.Combine(insertions), List.Combine(substitutions)}))
            in
                allVariants,
        searchVariants = generateVariants(lowerSearch),
        matches = List.AnyTrue(List.Transform(searchVariants, each Text.PositionOf(lowerSource, _, Occurrence.First) >= 0))
    in
        matches or Text.Contains(lowerSource, lowerSearch)

使用示例：

Text.ApproxContains("banana", "bannana") // 返回true，变体包含正确子串

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