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

[Anonymous]

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!

[Greg_Deckler]

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

[Anonymous]

Thanks!

Could you provide an example of the formula?

[Mariusz]

Hi @Anonymous 

You can add a Conditional Column like below.

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]

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

[Mariusz]

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]

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

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

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

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:

Best Regards

Allan

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

[amitchandak]

@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.