question

Steve-4038 avatar image
Steve-4038 asked ·

Power query, filter if text contains item from list

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-supported
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DSPatrick avatar image
DSPatrick answered ·

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.




Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.