Power query: Add column with list of keywords found in text column

[nhoff]

In power query I have a text column with phrases. I want to categorize the phrases by one or more keywords found in the text. The keywords are defined in another table.

I would like the found keywords to be added as a table/list in a new column. I have tried to demonstrate this in the inserted photo below. The reason I want a table returned is, that I intend to expand this.

Assuming my approach is not completely wrong, I need help to write the code in M.

[raymond]

Thanks for that great example. Helped me a lot.

[v-ljerr-msft · Accepted solution]

Hi @nhoff,

According to your description, using Table.SelectRows and Text.Contains functions should meet your requirement. You should be able to use the formula below to add the "table to be returned" column which returns a table and expand it.

= let CurrentText = [Text] in Table.SelectRows(Table2,each Text.Contains(CurrentText, [Key works to be searched for]))

Regards

[h4n1234]

thanks. This really helped me !

[Anonymous]

Hi @v-ljerr-msft! I am having the exact same problem and It looks like your formula could help me solving it. Could you please just clarify what "table 2" represents in your formula. I don't know what to plug in there. 

Thank you very much! 

[nhoff]

Very nice! Thanks a lot!

[ImkeF]

Pretty nice solution! With a 3rd parameter in the Text.Contains-function you can make it case insensitive to match the original requirement: "Comparer.OrdinalIgnoreCase"

like this:

= let CurrentText = [Text] in Table.SelectRows(Table2,each Text.Contains(CurrentText, [Key works to be searched for], Comparer.OrdinalIgnoreCase))

(My newest discovery 🙂 : http://www.thebiccountant.com/2016/10/27/tame-case-sensitivity-power-query-powerbi/)

[BraneyBI]

Imke - you rock!  Thanks for the case insensitivity trick!

[ImkeF]

Glad it helped @BraneyBI 🙂

[v-ljerr-msft]

Hi @ImkeF,

Great to know that! Thanks!

Regards