Forum Discussion

imnotabot01001's avatar
imnotabot01001
Regular Visitor
4 years ago
Solved

Filter rows by CONTAIN criteria from a list (AND)

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:

DateProduct_Name
1/1AA BB CC

1/2

BB CC XYZ
1/3AABB XYZ

1/4

BBCC

 

listA (criteria):

AA
XYZ

 

desired result:

DateProduct Name
1/3AABB 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)

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    You could do this:

     

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

     

    --Nate

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      imnotabot01001
      Regular Visitor
      4 years ago

      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's avatar
        PhilipTreacy
        Icon for Super User rankSuper User
        4 years ago

        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