Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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 Employee
Microsoft Employee

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

9 REPLIES 9
raymond
Post Patron
Post Patron

Thanks for that great example. Helped me a lot.

v-ljerr-msft
Microsoft Employee
Microsoft Employee

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

thanks. This really helped me !

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.