Forum Discussion
Powerquery > Advanced Filter not working with multiple clauses
=Table.SelectRows(#"Trimmed Text", each not List.Contains({"Word 1","Word 2","Word 3"},[Title],(x,y)=>Text.Contains(y,x)))
Thanks Vijay
Appreciate you going to the effort of building some custom code to fix this issue. I'm still pretty much a novice at using PowerQuery, so have no idea how to take you code and make the necessary amends needed to get it too work with my data. i.e. the example I gave in my original post used placeholder text of Word 1, Word 2, etc, which you have referenced in your code I see. But the actual words in my data are all unique words. So not sure how that would impact on your code?
Also I have no idea how to take your code and apply it to my original powerquery query which also has other steps to clean up the raw data.
I see you are a poweruser, I don't suppose you know why Microsoft's built in Advanced Filters is not working?
1. It will work. If your all words are unique, most likely reason is that it is not working because of case sensitivity. PQ is super case-sensitive. Whereever you have used Text.Contains, you need to use Comparer.OrdinalIgnoreCase parameter to switch off case-sensitivity.
Look below
Text.Contains([Title], "WORD 1",Comparer.OrdinalIgnoreCase)
2. There are other techniques also available like having a list of Words on which you want to performing filtering. But you already have a workable query which will give the result after using Comparer.OrdinalIgnoreCase.
-------------------------------------------------------------------------------------------------------
👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly -- How to provide sample data
-------------------------------------------------------------------------------------------------------