Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter one table based on contents of a second table

Being new to Excel's Power Query, I am faced with a challenge well beyond my skills. 

Rather than actual data, here is an example I hope illustrates my challenge: 

I have a table of phrases and a table of animals.  I need to eliminate any phrase that includes a reference to one of the listed animals.  The desired outcome is the table named Filtered Phrases.

Can someone point me to a generic solution to this class of problem?

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    Perfect, this is just what I needed.

     

    Thank you.

     

4 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion

    Anonymous  can you provide sample data and expected output in table format.

  • Here's one way using List.Accumulate to filter the column:

     

    let
        Source = Excel.CurrentWorkbook(){[Name="Phrases"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Phrases", type text}}),
    
    //Get keyword table and convert to list
        keyWords = Excel.CurrentWorkbook(){[Name="keyWords"]}[Content][Key Words],
    
    //create list with matching rows
    //  List.Accumulate creates the list
    //  Table.FromColumns turns it back into a table
        filteredPhrases = Table.FromColumns(
        {List.Accumulate(#"Changed Type"[Phrases],{},(state, current)=>
            if List.ContainsAny(Text.Split(current," "),keyWords) then state else state & {current})},
            type table[Phrases=text])
        
    in
        filteredPhrases

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Perfect, this is just what I needed.

       

      Thank you.

       

  • Add a filter step to your query and update the code in the formula bar along these lines:

    = Table.SelectRows(#"Prev Step Name", (r) =>
        List.IsEmpty(
             List.Select(Animals[Animals], each Text.Contains(r[Phrases], _))
        )
    )