Need help in doing keyword Search

Anonymous 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

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

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

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!

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

Thanks in advance

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

Hi Anonymous 

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