*** Be mindful with the words that you filter using these options. *** In the second option, Power Query will look in any of the columns listed in the first argument of List.ContainsAny, for any of the words listed in the second argument. So, if you have words that may be contained in several columns, but you just want to filter that word from one of those columns, then filter that column individually, don't use option 2. In the first options, =List.MatchesAny( ) will match any text that contains "total", so any item containing that word in the columns listed in List.MatchAny will be filtered. So, if the text may be contained in several columns listed in the first argument of List.MatchesAny, but you want to filter that text only from one of those columns, then filter that column individually, don't include it in option 1.
Glad it was helpful! Remember to be mindful with the words that you filter using these options. In the second option, Power Query will look in any of the columns listed in the first argument of List.ContainsAny, for any of the words listed in the second argument. So, if you have words that may be contained in several columns, but you just want to filter that word from one of those columns, then filter that column individually, don't use option 2. In the first options, =List.MatchesAny( ) will match any text that contains "total", so any item containing that word in the columns listed in List.MatchAny will be filtered. So, if the text may be contained in several columns listed in the first argument of List.MatchesAny, but you want to filter that text only from one of those columns, then filter that column individually, don't include it in option 1. Cheers! 😊
Great video, Alternatively we could do it with your suggested formula in one step as below: = Table.SelectRows(Source, each not(List.MatchesAny({[Zone],[Account Name],[Type]},each Text.Contains(_,"total",Comparer.OrdinalIgnoreCase))))
Nice video as always. One question: in the second example, I noticed that the list of words to return True are not in the same order as the list of columns. The list of columns is Dept, Account Name, Type, but for the list of words you have Dept, Type, Account Name. Since you still got the results to work, does this mean that power query will look in any of the 3 columns for any of the 3 words? If so, that could be problematic if, for example, you had a value of “other” in the Type column as well.
Thanks for your comment. That's is correct! The function will look for any match in any of the 3 columns. If you have the same word in those columns, and you want to filter it only from one of them, you can filter that column individually instead. Cheers! 😊
*** Be mindful with the words that you filter using these options. ***
In the second option, Power Query will look in any of the columns listed in the first argument of List.ContainsAny, for any of the words listed in the second argument. So, if you have words that may be contained in several columns, but you just want to filter that word from one of those columns, then filter that column individually, don't use option 2.
In the first options, =List.MatchesAny( ) will match any text that contains "total", so any item containing that word in the columns listed in List.MatchAny will be filtered. So, if the text may be contained in several columns listed in the first argument of List.MatchesAny, but you want to filter that text only from one of those columns, then filter that column individually, don't include it in option 1.
Great versatility using both these methods! Super appreciate you sharing as always!
Glad you like them! Thanks for watching 😊
Simplemente Magistral!... Saludos y gracias Ale.
Muchas gracias Eduardo, eres muy amable. Te mando muchos saludos 😊
Fantastic! Thank you so much. That's exactly what I was looking for!
Glad it was helpful!
Remember to be mindful with the words that you filter using these options.
In the second option, Power Query will look in any of the columns listed in the first argument of List.ContainsAny, for any of the words listed in the second argument. So, if you have words that may be contained in several columns, but you just want to filter that word from one of those columns, then filter that column individually, don't use option 2.
In the first options, =List.MatchesAny( ) will match any text that contains "total", so any item containing that word in the columns listed in List.MatchAny will be filtered. So, if the text may be contained in several columns listed in the first argument of List.MatchesAny, but you want to filter that text only from one of those columns, then filter that column individually, don't include it in option 1.
Cheers! 😊
Thank you. Love Power Query and videos like this showing the magic spells possible. 🙂
Glad you enjoy it! Thanks for watching 😊
Excellent as always ❤👍
Glad you liked it Kebin! Thanks for watching, cheers! 😊
Great video, Alternatively we could do it with your suggested formula in one step as below:
= Table.SelectRows(Source, each not(List.MatchesAny({[Zone],[Account Name],[Type]},each Text.Contains(_,"total",Comparer.OrdinalIgnoreCase))))
Thanks for sharing! Cheers! 😊
Amazing! :)
Glad you liked it! Thanks for watching 😊
Great 💯👍
Muchas gracias Ramiro! Un abrazo 😊
Nice video as always. One question: in the second example, I noticed that the list of words to return True are not in the same order as the list of columns. The list of columns is Dept, Account Name, Type, but for the list of words you have Dept, Type, Account Name.
Since you still got the results to work, does this mean that power query will look in any of the 3 columns for any of the 3 words? If so, that could be problematic if, for example, you had a value of “other” in the Type column as well.
Thanks for your comment. That's is correct! The function will look for any match in any of the 3 columns. If you have the same word in those columns, and you want to filter it only from one of them, you can filter that column individually instead. Cheers! 😊
👍
Thanks for watching 😊
In the second example, what would you do if all three columns had "Other" has a value but you only wanted to filter it out of one column?
That's a very good question. In that case option 2 wouldn't work. I would filter that column individually. Thanks for watching 😊
Can we do it by defining parameters?
I don't know, I have never tried.