Forum Discussion
Filter rows by CONTAIN criteria from a list (AND)
Download sample Excel file with query
Here's the code:
let Source = Excel.CurrentWorkbook(){[Name="TextTable"]}[Content], Result = Table.AddColumn(Source, "Check", (FindStrings) => List.AllTrue(List.Transform(WordList, each Text.Contains((FindStrings[Product_Name]), _ )))), #"Filtered Rows" = Table.SelectRows(Result, each ([Check] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Check"}) in #"Removed Columns"If you want to do further reading/see more examples of this kind of thing check here
Searching for Text Strings in Power Query • My Online Training Hub
regards
Phil
Super UserDownload sample Excel file with query
Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="TextTable"]}[Content],
Result = Table.AddColumn(Source, "Check",
(FindStrings) =>
List.AllTrue(List.Transform(WordList, each Text.Contains((FindStrings[Product_Name]), _ )))),
#"Filtered Rows" = Table.SelectRows(Result, each ([Check] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Check"})
in
#"Removed Columns"
If you want to do further reading/see more examples of this kind of thing check here
Searching for Text Strings in Power Query • My Online Training Hub
regards
Phil
Hi PhilipTreacy thanks for this it works!! exactly what I was looking for.
Not essential but, would there be a way to shorten code so there's only one step involved? Thanks again!
- PhilipTreacy
No not really. I mean you could move the code into a function and call that in one step but that's overcomplicating things.
You can rearrange the code like so if you want
let Source = Excel.CurrentWorkbook(){[Name="TextTable"]}[Content], Result = Table.AddColumn(Source, "Check", (FindStrings) => List.AllTrue(List.Transform(WordList, each Text.Contains((FindStrings[Product_Name]), _ )))), #"Filtered Rows" = Table.SelectRows(Result, each ([Check] = true)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Check"}) in #"Removed Columns"regards
Phil