Forum Discussion
Solved
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@12...
I just tried this and it worked .
if (Text.Contains([Subject],"trade")) and (Text.Contains([Subject],"fail")) and ( (Text.Contains([Contacts],"[email protected]")) or (Text.Contains([Contacts],"[email protected]")) ) 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],"[email protected]")) or (Text.Contains([Contacts],"[email protected]")) ) and not (Text.Contains([Subject],"trade")) and not (Text.Contains([Subject],"re:")) then "Report Failure" else ""
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],"[email protected]"))
then "Report Failure"
else if (Text.Contains([Subject],"trade"))
and (Text.Contains([Subject],"fail"))
and (Text.Contains([Contacts],"[email protected]"))
then "Trade Failure"
I just tried this and it worked .
if (Text.Contains([Subject],"trade"))
and (Text.Contains([Subject],"fail"))
and (
(Text.Contains([Contacts],"[email protected]"))
or
(Text.Contains([Contacts],"[email protected]"))
)
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],"[email protected]"))
or
(Text.Contains([Contacts],"[email protected]"))
)
and not (Text.Contains([Subject],"trade"))
and not (Text.Contains([Subject],"re:"))
then "Report Failure"
else ""