cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

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

 

 

1 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 ""

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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"))

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"

 

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 ""

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors