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
Anonymous
Not applicable

Tag rows containing strings among a list

Hello community,

 

I'm stuck on a "simple" problem: In a table, I want to flag rows containing some words comming from a list.

 

Exemple:

DataTable

Id - Phrase - Flag (Subject of this topic)

1 - I am old - true

2 - I am young - false

3 - I am tall - false

4 - I am tiny - true

...

WordList

blue; big; young; sport; tiny...

 

Because I have a lot of words in my wordlist, I had created a dedicated table WordList with 1 column containing all words on each rows.

 

I tryed to use a calculated column in my dataTable but impossible to find the right formula.

 

Can anyone help me please?

Thank you.

Fabien

 

1 ACCEPTED SOLUTION
lc_finance
Solution Sage
Solution Sage

Hi @Anonymous ,

 

 

here is the solution I propose: create a calculated column in the phrases table with the following formula

Flag = 
SUMX('Wordlist',
IF(ISERROR(SEARCH([Words], 'Phrases'[Phrases])),0,1)
)

For each phrase, the formula checks the wordlist and puts 1 if it finds the word and 0 otherwise.

Image 2019-10-17 at 7.54.05 PM.png

 

Hope this is what you are looking for!

 

Regards,

 

LC

Interested in Power BI dashboards? Check out my blog at www.finance-bi.com

 

View solution in original post

3 REPLIES 3
lc_finance
Solution Sage
Solution Sage

Hi @Anonymous ,

 

 

here is the solution I propose: create a calculated column in the phrases table with the following formula

Flag = 
SUMX('Wordlist',
IF(ISERROR(SEARCH([Words], 'Phrases'[Phrases])),0,1)
)

For each phrase, the formula checks the wordlist and puts 1 if it finds the word and 0 otherwise.

Image 2019-10-17 at 7.54.05 PM.png

 

Hope this is what you are looking for!

 

Regards,

 

LC

Interested in Power BI dashboards? Check out my blog at www.finance-bi.com

 

Anonymous
Not applicable

Thank you very much @lc_finance, it's working perfectly!

 

I was close but I miss some experience with Power BI 🙂

 

Have a nice day.

Fabien

Very good to hear that it's working for you!

 

LC

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.