Power Query Function to search for matching keywords in a table of lists and return the text in the cel in front of the matching row

Question

I have a similar problem but a bit more complex as this one : Power Query: Function to search a column for a list of keywords and return only rows with at least one match and this one : https://community.powerbi.com/t5/Desktop/Power-query-Add-column-with-list-of-keywords-found-in-text/td-p/83109

I have a Database with a lot of columns of which one is a free-text description string. On another Excel Sheet in the workbook, I've set up a Matching table to categorize the rows based on lists of keywords like this :

• category | keywords
• pets | dog, cat, rabbit,...
• cars | Porsche, BMW, Dodge,...
• ...

The goal is to put a custom column in my database that will return the hereabove category (or categories ?) based on which listed keywords it can find in the description field.

I think the solution above and the one from ImkeF are not so far but I didn't find a way to turn it into a successful Query for my case. (I'm good at Excel but quite a noob to M and programming Queries...)

Answer 1

oriented on the obove posted links:

M-Code for tbl_category: the keywords (separated with comma) will be split into rows

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_category"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"keywords"}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value", {{"keywords", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "keywords"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"keywords", type text}})
in
    #"Changed Type1"

M-Code for tbl_text. Here will be add a Custom Column called "Category":

let
    Source = Excel.CurrentWorkbook(){[Name="tbl_text"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Category", (Earlier) => Table.SelectRows(tbl_category, 
     each Text.Contains(Record.Field(Earlier, "Text"), Record.Field(_, "keywords"), Comparer.OrdinalIgnoreCase))),
    #"Expanded Category" = Table.ExpandTableColumn(#"Added Custom", "Category", {"Category"}, {"Category"})
in
    #"Expanded Category"