Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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
Thanks for that great example. Helped me a lot.
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
thanks. This really helped me !
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
87 | |
84 | |
67 | |
49 |
User | Count |
---|---|
141 | |
115 | |
111 | |
59 | |
59 |