Power BI中Power Query检查列值是否为其他字符串子串并标记

实现逻辑 #

校验规则拆解为两个判断层级，完全匹配示例输出：

1. 若当前行字符串是同列任意其他行更长字符串的子串，直接标记为already exists
2. 若不满足上一条，再判断当前行是否为同值字符串中最早出现的行：是则标记为unique，否则标记为already exists

操作步骤 #

• 第一步：数据导入Power Query编辑器后，先添加索引列固定行顺序：点击「添加列」选项卡→「索引列」→「从0开始」，新增列默认名称为Index
• 第二步：添加自定义步骤缓存全量字符串列表，避免逐行计算重复拉取数据、提升性能：在「应用步骤」面板点击最后一个步骤，在公式栏输入以下公式（假设你上一步的表名称为AddedIndex，待校验字符串列名称为string，可根据实际命名替换）：

BufferedStringList = List.Buffer(AddedIndex[string])

• 第三步：新增自定义列存放校验结果，点击「添加列」→「自定义列」，新列名设为result，输入以下公式：

if List.AnyTrue(
    List.Transform(
        BufferedStringList,
        (t) => Text.Length(t) > Text.Length([string]) and Text.Contains(t, [string])
    )
) then "already exists"
else if List.Min(Table.SelectRows(AddedIndex, each [string] = [string])[Index]) = [Index] then "unique"
else "already exists"

• 第四步：点击确定即可完成校验，输出结果和示例规则完全匹配。如果后续不需要索引列，直接删除即可。

注意事项 #

• 上述Text.Contains默认区分大小写，如果需要校验时忽略大小写，可替换为Text.Contains(t, [string], Comparer.OrdinalIgnoreCase)
• 不要直接逐行引用原表列做遍历，会触发Power Query的重复计算机制导致运行速度极慢，必须先用List.Buffer将字符串列表加载到内存缓存
• 如果数据量超过10万行，可以提前将字符串按长度分组缓存，进一步减少逐行遍历的计算量

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