Power query, filter if text contains item from list

Steve 21 Reputation points

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.

Thank you in advance

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
27,016 questions
0 comments No comments
{count} votes

Accepted answer
  1. Dave Patrick 352.3K Reputation points MVP

    Power query is not currently supported here on QnA. They're actively answering question in dedicated forums here.


    --please don't forget to Accept as answer if the reply is helpful--

    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    0 comments No comments

0 additional answers

Sort by: Most helpful