cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nhoff
Advocate I
Advocate I

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

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.

 

Udklip.JPG

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft
Microsoft

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]))

add column.PNG

table.PNG

expand.PNG

 

Regards

View solution in original post

8 REPLIES 8
raymond
Post Patron
Post Patron

Thanks for that great example. Helped me a lot.

v-ljerr-msft
Microsoft
Microsoft

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]))

add column.PNG

table.PNG

expand.PNG

 

Regards

View solution in original post

Anonymous
Not applicable

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! 

 

 

Very nice! Thanks a lot!

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/)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

Glad it helped @BraneyBI 🙂

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi @ImkeF,

 

Great to know that! Thanks!Smiley Happy

 

Regards

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.