Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.