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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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