Filter if text contains an item from a list. RRS feed

  • Question

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

    I've tried the formula:

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

    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.

    Friday, June 12, 2020 8:11 PM


  • Hi

    You can use a custom function like fxMatchInList

    (Values as any, Items as anynonnull, optional IgnoreCase) as any =>
        NbListItems = List.Count(Items),
        Sensitivity = if IgnoreCase = true 
            then Comparer.OrdinalIgnoreCase else Comparer.Ordinal,
        Value = Text.Combine(Values),
        Match = List.Generate(
            ()=> [i=0, out=if Text.Contains(Value,Items{i},Sensitivity) then Items{i} else null],
                        each [i] < NbListItems,
                                i = if [out] <> null then [i]+NbListItems else [i]+1,
                                out = if Text.Contains(Value,Items{i},Sensitivity)
                                      then Items{i} else null
                        each [out]
        NoNull = List.RemoveNulls(Match),
        Result = if List.IsEmpty(NoNull) then null else List.First(NoNull)
        if List.NonNullCount(Values) = 0 is null then null else Result

    and in your main query, assuming your last step before filtering is ChangedType:

        MED_NAME_LIST = List.Buffer({"methylprednisolone","hydroxychloroquine","remdesivir"}),
        Matched = Table.AddColumn(ChangedType, "Match", each
            fxMatchInList({[Name],[GenericName],[SimpleGenericName]}, MED_NAME_LIST, true), type text),
        FilteredNull = Table.SelectRows(Matched, each ([Match] <> null))

    Corresponding sample avail. here
    Saturday, June 13, 2020 6:26 AM