Power Query M formula not working ( Nested IF with and)

[galbatrox9]

Hi Team,

I am trying to add a custom column based on two columns. I want the new column to tell me all rows where the Subject contains "trade" and "fail" but Contacts does not contain no-reply@123.com.

if (Text.Contains([Subject],"trade"))
and (Text.Contains([Contacts],"no-reply@123.com"))
and (Text.Contains([Subject],"fail"))
and not Text.Contains([Subject],"scheduled")
then "Trade Failure"
else ""

But the output is blank. When i run the code without the and not line, its working (but obviously not pulling what i want).

[Anonymous]

What if you try:

IF (Text.Contains([Subject],"scheduled"), "", 

if (Text.Contains([Subject],"trade"))
and (Text.Contains([Contacts],"no-reply@123.com"))
and (Text.Contains([Subject],"fail"))

) 

[galbatrox9]

Well there is another IF that i was wanting to nest into it so this wont work.

This is the currently the code with the nested if, but it doesn't work. 

if (Text.Contains([Subject],"scheduled"))
and (Text.Contains([Contacts],"no-reply@123.com"))
then "Report Failure"

else if (Text.Contains([Subject],"trade"))
and (Text.Contains([Subject],"fail"))
and (Text.Contains([Contacts],"no-reply@123.com"))
then "Trade Failure"

[galbatrox9 · Accepted solution]

I just tried this and it worked .

if (Text.Contains([Subject],"trade"))
and (Text.Contains([Subject],"fail"))
and (
    (Text.Contains([Contacts],"noreply@123.com"))
or 
(Text.Contains([Contacts],"no-reply@123.com"))
)

and not (Text.Contains([Subject],"scheduled"))
and not (Text.Contains([Subject],"re:"))
then "Trade-In Failure"

else if (Text.Contains([Subject],"scheduled"))
and (Text.Contains([Subject],"fail"))
and (
    (Text.Contains([Contacts],"noreply@123.com"))
or 
(Text.Contains([Contacts],"no-reply@123.com"))
)

and not (Text.Contains([Subject],"trade"))
and not (Text.Contains([Subject],"re:"))
then "Report Failure"

else ""