Forum Discussion
Need help in doing keyword Search
Hi All, I am currently using a list to do a 'keyword search' in Power Query. Basically i have a list like "Apple" or "Pear" and i will check whether my record in the table will have Apple or Pear...
Hi Anonymous
You can add a custom column in table and input the following code.
=List.Accumulate(List.Numbers(0,Table.RowCount(KeywordSearchB),1),"No",(state,current)=>if Text.Contains(Text.Lower([Product Title]),KeywordSearchB[KeywordSearch]{current}) then "Yes" else state)For the keywordsearcha table, just replace the
it can work.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous Try below M Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}),
KeywordList = {"Apple", "Pear"},
ChkKeywords = (text as text, keyword as list) as logical =>
List.AnyTrue(List.Transform(keyword, each Text.Contains(text,_))),
AddCustomColumn = Table.AddColumn(#"Changed Type", "ContainsKeyword", each ChkKeywords([List], KeywordList))
in
AddCustomColumn
Orginal Table:
After transformation:
This will enhance performance of the query. Also you can use List.Buffer to load the list into memory, reducing the number of times Power Query needs to access the list during the search.
let
Source = ...,
KeywordList = List.Buffer({"Apple", "Pear"}),
AddCustom = Table.AddColumn(Source, "ContainsKeyword", each List.AnyTrue(List.Transform(KeywordList, each Text.Contains([YourColumn], _))))
in
AddCustom
Hope this helps!!
If this solved your problem, please accept it as a solution!!
Best Regards,
Shahariar Hafiz