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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Add new column which search an existing one, and if some text strings are present return them

Hi,

 

I am trying to add a new column in Power Query which would need to do something very simple:

 

search an existing column which includes text values (products) and if it find some matches it returns the same content of the related cell (if not, empty).

 

The create conditional column does not seem to figure this out, I tried with a mix of 'contains' and 'not contains' but it does not get it.

What would be a DAX formula to do that? 

I've been browsing online, but could not find the specific case I am looking for.

 

Thanks!

1 ACCEPTED SOLUTION

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario.

a1.png

 

You may create a calculated column as follows.

 

ResultColumn = 
IF(
    AND(
        OR(
            CONTAINSSTRINGEXACT('Table'[Column1],"Red"),
            CONTAINSSTRINGEXACT('Table'[Column1],"Green")
        ),
        NOT(CONTAINSSTRINGEXACT('Table'[Column1],"Yellow"))
    ),
    'Table'[Column1],
    BLANK()
)

 

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Greg_Deckler
Community Champion
Community Champion

In DAX you would use SEARCH or FIND (or IN) and an IF statement in your RETURN that returned BLANK if not found. 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks!

Could you provide an example of the formula?

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

You can add a Conditional Column like below.

image.png

If this is not what you after then please provide an example.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous
Not applicable

Hi ,

 

thanks -  as I said, that does not seem to work. It does at a very basic level, like the example you showed.

 

But If I want that more refined and I add some extra claues (e.g. with a 'does not include' 'ab') it seems not being realiable anymore (e.g. things which should be filtered out are kept in).

 

Thanks

Hi @Anonymous 

 

Please can you create a data sample to support your scenario?

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Anonymous
Not applicable

Hi,

 

please see here below a rather simplified version of the scenario I am dealing with: 

 

 

COLUMN1

COLUMN I'd like to return (conditions)

 
Cat.1 Red, Pink, Blue, Yellow

If C1 inlcudes Red OR Green return C1

AND

If C1 does NOT include Yellow return C1

 

Else, return empyt

 

Cat.2 Red, Yellow,Green

  
Cat.3 Black,Yellow  

Hi, @Anonymous 

 

Based on your description, I created data to reproduce your scenario.

a1.png

 

You may create a calculated column as follows.

 

ResultColumn = 
IF(
    AND(
        OR(
            CONTAINSSTRINGEXACT('Table'[Column1],"Red"),
            CONTAINSSTRINGEXACT('Table'[Column1],"Green")
        ),
        NOT(CONTAINSSTRINGEXACT('Table'[Column1],"Yellow"))
    ),
    'Table'[Column1],
    BLANK()
)

 

 

Result:

a2.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@Anonymous ,

Try a new column like

SWITCH(true(),
search("Red",[column1],1,0)>0 || search("Green",[column1],1,0)>0 ,"C1"
search("Yellow",[column1],1,0)<0 , "C1",
blank()
)

 

if you need more help make me @

Appreciate your Kudos.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors