Need help in doing keyword Search

[chinaik123]

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 inside. If it does , i will return it as Yes. However, this method is exhaustive as I spent about 6-10 minutes just waiting for it to refresh.

Could anyone advise if there is a better way to this?

Thanks in advance.

[v-xinruzhu-msft]

Hi @chinaik123 

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.

[v-xinruzhu-msft]

Hi @chinaik123 

Based on your description, What does the column you are referring to look like? Can you provide some pictures so that I can provide you with more help? Please hide your private information on the photos.

Best Regards!

Yolo Zhu

Best Regards!

Yolo Zhu

[chinaik123]

Hi Yolo,
This is a sample of how it looks like.
This is my mapping tables where it will need to find keywords that is related to all this.
It is based in a file in Sharepoint

and My Data looks like this:

so i will need to search if Product Title consist of the keyboard shown in the above table in SharePoint and return No. 
I have used a Power Query to get the Mapping table as shown above

[Anonymous]

Is the value that you are checking for in a specific column in the SharePoint table? When you say "records" do you mean actual record values, or are we talking about just regular table rows as records?

Anyway, if the value could be in any field in the record, I would expand the records and use each Table.FindText(SearchTable, [Column With Fruit Names That You Turned Into A Table])

--Nate

[chinaik123]

Hi @shafiz_p , Would you advise on the code if my list was extracted via Power Query instead?

Thanks in advance

[chinaik123]

Hi @BeaBF , I would need your help to integrate to my code. However, the example of Apple,Pear list is a another list that i am querying in SharePoint, hence i guess i wouldnt be able to follow the code as provided?

Thanks in advance!

[BeaBF]

@chinaik123 Can you oaste the code of nte advanced editor?

BBF

[chinaik123]

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]

@chinaik123 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

[Omid_Motamedise]

It depend to your data structure,

if your table comes with low number of column, use Table.SelectRows, if the number of columns are to much, use list.generate to do it column by column and as find it break the loop

[shafiz_p]

Hi @chinaik123  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

[BeaBF]

@chinaik123 Hi! Instead of checking each keyword individually, use the List.Contains function to check if any of the keywords are present in your records. This method is typically more efficient. Here a M code example:

let
// Sample Data
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Keywords = {"Apple", "Pear"}, 

// Add a column to check if any keyword is present in the Record
AddedCustom = Table.AddColumn(Source, "Contains Keyword",
each if List.Contains(Keywords, Text.Trim(Text.Lower([YourColumnName]))) then "Yes" else "No"
)
in
AddedCustom

let me know if you want support in adapting your code. If it's ok, please accept my answer as solution.

BBF