Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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], _))
)
)
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
Perfect, this is just what I needed.
Thank you.
@YardiDave can you provide sample data and expected output in table format.