Filter rows by CONTAIN criteria from a list (AND)

[imnotabot01001]

Hi- my table needs to be filtered by a list. Text contained in list should be used as an AND criteria (only show rows that contain all texts from list). Help please 😞

my table:

Date	Product_Name
1/1	AA BB CC
1/2	BB CC XYZ
1/3	AABB XYZ
1/4	BBCC

listA (criteria):

AA

XYZ

desired result:

Date	Product Name
1/3	AABB XYZ

*I've done a NOT criteria filter based on the below. this acts like an OR , whereas I need an AND 

Table.SelectRows(#"Added Custom", each List.Count(Splitter.SplitTextByAnyDelimiter(listA)([Product_Name])) > 1)

[PhilipTreacy · Accepted solution]

Hi @imnotabot01001 

Download sample Excel file with query

Here's the code:

let
    Source = Excel.CurrentWorkbook(){[Name="TextTable"]}[Content],
    Result = Table.AddColumn(Source, "Check", 
    
        (FindStrings) => 
        
        List.AllTrue(List.Transform(WordList, each Text.Contains((FindStrings[Product_Name]), _ )))),
    #"Filtered Rows" = Table.SelectRows(Result, each ([Check] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Check"})

in
    #"Removed Columns"

If you want to do further reading/see more examples of this kind of thing check here

Searching for Text Strings in Power Query • My Online Training Hub

regards

Phil

[imnotabot01001]

Hi @PhilipTreacy thanks for this it works!! exactly what I was looking for. 

Not essential but, would there be a way to shorten code so there's only one step involved? Thanks again!

[PhilipTreacy]

Hi @imnotabot01001 

No not really.  I mean you could move the code into a function and call that in one step but that's overcomplicating things.

You can rearrange the code like so if you want

let
    Source = Excel.CurrentWorkbook(){[Name="TextTable"]}[Content],
    Result = Table.AddColumn(Source, "Check", (FindStrings) => List.AllTrue(List.Transform(WordList, each Text.Contains((FindStrings[Product_Name]), _ )))),
    #"Filtered Rows" = Table.SelectRows(Result, each ([Check] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Check"})
in
    #"Removed Columns"

regards

Phil

[Anonymous]

You could do this:

Table.SelectRows(#"Added Custom", each Text.Contains(Text.Combine(listA), [Product_Name]))

--Nate