Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

BeaBF's avatar
BeaBF
Super User
1 year ago

Anonymous Can you oaste the code of nte advanced editor?

 

BBF

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi BeaBF 

Please find the code as shown:

let
    Source = Table.Combine({Tokopedia, Lazada, Shopee}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Product ID"}, ProductMapping, {"product_id"}, "ProductMapping", JoinKind.LeftOuter),
    #"Expanded ProductMapping" = Table.ExpandTableColumn(#"Merged Queries", "ProductMapping", {"BPM?"}, {"ProductMapping.BPM?"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded ProductMapping", {"Avg. Selling Price"}, #"Pricing Ranges", {"Price More Than"}, "Pricing Ranges", JoinKind.FullOuter),
    #"Expanded Pricing Ranges" = Table.ExpandTableColumn(#"Merged Queries1", "Pricing Ranges", {"Price More Than", "Column2"}, {"Pricing Ranges.Price More Than", "Pricing Ranges.Column2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Pricing Ranges", "Price Range", each if [Avg. Regular Price] = null then [Pricing Ranges.Price More Than] else [Avg. Regular Price]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Price Range", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Price Range", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Pricing Ranges.Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Pricing Ranges.Price More Than", "Price Range"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Seller Name] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom2" = Table.AddColumn(#"Added Index", "KeywordSearchA", each let
    Keywords = {"Tensi", "Panasonic"},
    ContainsKeywords = List.AnyTrue(List.Transform(Keywords, (keyword) => Text.Contains([Product Title], keyword, Comparer.OrdinalIgnoreCase)))
in
    if ContainsKeywords then "Yes" else "No"),
    #"Added Custom" = Table.AddColumn(#"Added Custom2", "KeywordSearch", each KeywordSearchB),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "KeywordSearchB", each let
    Keywords = KeywordSearchB[KeywordSearch],
    ContainsKeyword = List.AnyTrue(List.Transform(Keywords, (keyword) => Text.Contains([Product Title], keyword, Comparer.OrdinalIgnoreCase)))
in
    if ContainsKeyword then "No" else "Yes")
in
    #"Added Custom1"
  • BeaBF's avatar
    BeaBF
    Super User
    1 year ago

    Anonymous Try with:

     

    let
    Source = Table.Combine({Tokopedia, Lazada, Shopee}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Product ID"}, ProductMapping, {"product_id"}, "ProductMapping", JoinKind.LeftOuter),
    #"Expanded ProductMapping" = Table.ExpandTableColumn(#"Merged Queries", "ProductMapping", {"BPM?"}, {"ProductMapping.BPM?"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded ProductMapping", {"Avg. Selling Price"}, #"Pricing Ranges", {"Price More Than"}, "Pricing Ranges", JoinKind.FullOuter),
    #"Expanded Pricing Ranges" = Table.ExpandTableColumn(#"Merged Queries1", "Pricing Ranges", {"Price More Than", "Column2"}, {"Pricing Ranges.Price More Than", "Pricing Ranges.Column2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Pricing Ranges", "Price Range", each if [Avg. Regular Price] = null then [Pricing Ranges.Price More Than] else [Avg. Regular Price]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Price Range", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Price Range", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Pricing Ranges.Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Pricing Ranges.Price More Than", "Price Range"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Seller Name] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),

    // Define the keyword list outside of the row transformation
    KeywordList = {"Tensi", "Panasonic"},

    // Custom function to check if any keyword is present in the text
    IsKeywordPresent = (text as text, keywords as list) as logical =>
    List.AnyTrue(List.Transform(keywords, each Text.Contains(text, _, Comparer.OrdinalIgnoreCase))),

    // Add the keyword search column using the custom function
    #"Added Keyword Search Column" = Table.AddColumn(#"Added Index", "KeywordSearchA", each if IsKeywordPresent([Product Title], KeywordList) then "Yes" else "No")

    in
    #"Added Keyword Search Column"

     

    BBF