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
GilesWalker
Skilled Sharer
Skilled Sharer

Finding a specific word in a text string

Hi everyone,

 

I have a column which contains text and I need a formula which can find a specific word (Magnetite) and if it finds it in the string to then give me what I want. 

 

I have tried the following formula:

 

IF(SEARCH("Magnetite",GLF_LDG_ACC_TRANS,GLF_LDG_ACC_TRANS[NARR1]),"Customer1","Customer2")

 

IF(CONTAINS(TABLE,GLF_LDG_ACC_TRANS[NARR1],"Magnetite"),"Customer1","Customer2")

 

However it just gives me the answer as Customer2.

 

Here is a picture of the data:

 

Power BI 1.PNG

 

Thanks,

 

Giles

1 ACCEPTED SOLUTION

I assume you are doing this in a calculated column, right?  If so, there is either a bug in the software or a bug in the documentation.  The documentation says the last 2 parameters are optional, but in my experience they are required.

 

This should work

=
IF (
ISBLANK ( SEARCH ( "Magnetite", GLF_LDG_ACC_TRANS[NARR1], 1, BLANK () ) ),
"Customer1",
"Customer2"
)

 

assuming I have the columns right and the returned values correct.  They may be back to front or wrong



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

7 REPLIES 7
Rodrigo
Frequent Visitor

Hi there, 

 

So I am having a similar situation but instead of one word I need to find multiple words. On a new column I would like to get a specific word from a text: 

 

Example: 

 

Column 1          

texttexttextUCCCalciotexttexttext

texttexttextUCCMilktexttexttext

texttexttexttexttexttexttexttexttextVitamins

texttexttextWatertexttexttext

 

Column 2

Calcio

Milk

Vitamins

Water

 

 

Thanks in advance

Anonymous
Not applicable

In similar situation. Were you able to get the solution for this one,

Anonymous
Not applicable

@Rodrigo did you find a solution for your problem? If so, could you please help me out?

I assume you are doing this in a calculated column, right?  If so, there is either a bug in the software or a bug in the documentation.  The documentation says the last 2 parameters are optional, but in my experience they are required.

 

This should work

=
IF (
ISBLANK ( SEARCH ( "Magnetite", GLF_LDG_ACC_TRANS[NARR1], 1, BLANK () ) ),
"Customer1",
"Customer2"
)

 

assuming I have the columns right and the returned values correct.  They may be back to front or wrong



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Anonymous
Not applicable

Hey Matt,

 

Is there any way to add a second or subsequent keyword matches in a single statement?  

 

My issue is I'm trying to find a search slicer that will not just search the possible lookups in that column, but actually update data on other visuals when I search a term.  For instance, I want to find keywords in social media posts, but as I search in the slicer I want it to update impression/engagements, etc.  Your formula works great, but it only works on a single keyword.

 

Thanks!

Anonymous
Not applicable

Thanks Matt for this solution - it helped me out as well!

thanks @MattAllington your solution of adding in the optional fields worked perfectly. I had to change the customer names around however the formula gave me what I needed.

 

Giles

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.