Power query, filter if text contains item from list

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.

Thank you in advance

Answer 1

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

https://social.technet.microsoft.com/Forums/en-US/home?forum=powerquery

--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.