Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YardiDave
Regular Visitor

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?

YardiDave_0-1639507152642.png

 

1 ACCEPTED SOLUTION

Perfect, this is just what I needed.

 

Thank you.

 

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

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], _))
    )
)

 

ronrsnfld
Super User
Super User

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

 

ronrsnfld_0-1639516100206.png

 

 

Perfect, this is just what I needed.

 

Thank you.

 

smpa01
Super User
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors