时间戳

首页 > 解决方案 > 列值等于列表项，如果项是多个列之一中的子字符串

excel - 列值等于列表项，如果项是多个列之一中的子字符串

问题描述

使用 PowerQuery 我想创建一个新列，其值等于列表 {MED_NAME_LIST} 中的一个项目，如果该项目是多个列之一中的子字符串：[Name]、[GenericName]、[SimpleGenericName]。

在上一篇文章中，我询问了如何根据多个文本列（[Name]、[GenericName] 或 [SimpleGenericName]）是否包含列表中的子字符串进行过滤。

Powerquery，字符串是否包含列表中的项目

该解决方案运行良好，但现在我意识到我还需要知道匹配的列表项是什么。

以前基于列表进行过滤的解决方案：

    MED_NAME_LIST = {"MEthYlprednisolone", "hYdroxychloroquine", "rEMdesivir"},
    initialTable = Table.FromRows({
        {"Methylprednisolone Tab", "train", "car", "bike"},
        {"no", "no", "no", "no"},
        {"tram", "teleport", "hydroxychloroQuine Tab", "jet"},
        {"no", "no", "no", "yes"},
        {"REMdesivir Tab", "bus", "taxi", "concord"}
    }, type table [Name = text, GenericName = text, SimpleGenericName = text, SomeOtherColumn = text]),
    filtered = Table.SelectRows(initialTable, each List.ContainsAny(
        {[Name], [GenericName], [SimpleGenericName]},
        MED_NAME_LIST,
        (rowValue as text, medicineFromList as text) as logical => Text.Contains(rowValue, medicineFromList, Comparer.OrdinalIgnoreCase)
    ))
in
    filtered```

标签： excelpowerbipowerquery

解决方案

自定义将是最后一场比赛。

如果有多个匹配项，Custom.1 将是所有匹配项的列表

let MED_NAME_LIST = {"MEthYlprednisolone", "hYdroxychloroquine", "rEMdesivir"},
initialTable = Table.FromRows({
    {"Methylprednisolone Tab", "train", "car", "bike"},
    {"no", "no", "no", "no"},
    {"tram", "teleport", "hydroxychloroQuine Tab", "jet"},
    {"no", "no", "no", "yes"},
    {"REMdesivir Tab", "bus", "taxi", "concord"}
}, type table [Name = text, GenericName = text, SimpleGenericName = text, SomeOtherColumn = text]),
filtered = Table.SelectRows(initialTable, each List.ContainsAny(
    {[Name], [GenericName], [SimpleGenericName]},
    MED_NAME_LIST,
    (rowValue as text, medicineFromList as text) as logical => Text.Contains(rowValue, medicineFromList, Comparer.OrdinalIgnoreCase)
)),
#"Added Custom" = Table.AddColumn(filtered, "Custom", each List.Accumulate(List.Transform(MED_NAME_LIST, each Text.Lower(_)), null, (state, current) => if Text.Contains(Text.Lower([Name]&[GenericName]&[SimpleGenericName]), current) then current else state)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Accumulate(List.Transform(MED_NAME_LIST, each Text.Lower(_)), {}, (state, current) => if Text.Contains(Text.Lower([Name]&[GenericName]&[SimpleGenericName]), current) then List.Combine({{current}, state}) else state))
in #"Added Custom1"

推荐阅读