Powerquery，字符串是否包含列表中的项目 [英] Powerquery, does string contain an item in a list

问题描述

我想过滤是否多个文本列([名称]，[GenericName]，或 [SimpleGenericName])包含列表中的子字符串.文本也是大小写混合的，所以我也需要在其中做一个Text.Lower([Column]).

I would like to filter on whether multiple text columns ([Name], [GenericName], or [SimpleGenericName]) contains a substring from a list. The text is also mixed case so I need to do a Text.Lower([Column]) in there as well.

我尝试过公式:

= Table.SelectRows(#"Sorted Rows", each List.Contains(MED_NAME_LIST, Text.Lower([Name])))

但是，由于[Name]列与列表中的那些项不完全匹配，因此此操作不起作用(例如，如果列表中包含"methylprednisolone"，则不会选择"Methylprednisolone Tab")

However, this does not work as the Column [Name] does not exactly match those items in the list (e.g. it won't pick up "Methylprednisolone Tab" if the list contains "methylprednisolone")

一个有效的过滤器示例，其中列出了所有列表:

An example of a working filter, with all some of the list written out is:

= Table.SelectRows(#"Sorted Rows", each Text.Contains(Text.Lower([Name]), "methylprednisolone") or Text.Contains(Text.Lower([Name]), "hydroxychloroquine") or Text.Contains(Text.Lower([Name]), "remdesivir") or Text.Contains(Text.Lower([GenericName]), "methylprednisolone") or Text.Contains(Text.Lower([GenericName]), "hydroxychloroquine") or Text.Contains([GenericName], "remdesivir") or Text.Contains(Text.Lower([SimpleGenericName]), "methylprednisolone") or Text.Contains(Text.Lower([SimpleGenericName]), "hydroxychloroquine") or Text.Contains([SimpleGenericName], "remdesivir"))

我想使这种方法更清洁，而不必将所有内容都写出来，因为我还希望能够扩展引用表中的列表以使其成为动态搜索.

I would like to make this cleaner than having to write all of this out, as I would also like to be able to expand the list from a referenced table to make this a dynamic search.

提前谢谢

推荐答案

如果我有药品清单:

我需要过滤我的表:

仅保留某些列(我们稍后将指定哪些列)包含不区分大小写的部分匹配项的行，这些匹配项与上述药物列表中的任何项均不区分大小写，那么执行此操作的一种方法可能是:

to only keep rows where certain columns (we'll specify which ones exactly later) contain case-insensitive, partial matches for any of the items in the above list of medicines, then one way to do this might be:

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)
    ))
in
    filtered

• 在 filtered 中， List.ContainsAny 用于确定是否有任何指定的列( Name ， GenericName ， SimpleGenericName )包含 MED_NAME_LIST 中任何值的匹配项".
• 匹配"的条件是:
  • 必须区分大小写(因此使用 Comparer.OrdinalIgnoreCase )
  • 匹配项必须是部分匹配项(因此使用 Text.Contains )
  • In filtered, List.ContainsAny is used to determine if any of the specified columns (Name, GenericName, SimpleGenericName) contain a "match" for any of the values in MED_NAME_LIST.
  • The criteria for the "match" is that:
    • case sensitivity must be ignored (hence Comparer.OrdinalIgnoreCase is used)
    • the match must be partial (hence Text.Contains is used)

    上面的代码为我提供了以下内容，我相信这是您描述的过滤行为:

    The above code gives me the following, which I believe is the filtering behaviour you described:

    这篇关于Powerquery，字符串是否包含列表中的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！