Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
In DAX you would use SEARCH or FIND (or IN) and an IF statement in your RETURN that returned BLANK if not found.
Thanks!
Could you provide an example of the formula?
Hi @Anonymous
You can add a Conditional Column like below.
If this is not what you after then please provide an example.
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?
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.
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |