Forum Discussion
Anonymous
4 years agoNot applicable
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?
- Anonymous4 years ago
Perfect, this is just what I needed.
Thank you.
4 Replies
- smpa01
Community Champion
Anonymous can you provide sample data and expected output in table format.
- ronrsnfld
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- AnonymousNot applicable
Perfect, this is just what I needed.
Thank you.
- AlexisOlson
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], _)) ) )