Forum Discussion

rosamhernandez1's avatar
rosamhernandez1
Icon for Helper I rankHelper I
9 months ago
Solved

Nested IF

Need help adjusting formula to only be calculated when the Development Status is "Active-Dev" or "Active-Exe"   Original formula is:  if [Interconnection Facilities ISD] <> null and Duration.Days...
Need Help
  • amitchandak's avatar
    amitchandak
    9 months ago

    rosamhernandez1 , Power query code 

    = if [Development Status] <> "Active – Dev" and [Development Status] <> "Active – Exe" then
    null
    else
    if [Interconnection Facilities ISD] <> null
    and Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) >= 0
    and Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) <= 30
    then "Yes"
    else "No"

Nabha-Ahmed's avatar
Nabha-Ahmed
Icon for Super User rankSuper User
9 months ago

Hi Zanqueta 

You need to check the Development Status first, and then run your original test block inside a nested if…then…else.

✅ Correct M Code

if [Development Status] = "Active-Dev"
or [Development Status] = "Active-Exe" then
if [Interconnection Facilities ISD] <> null and
Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) >= 0 and
Duration.Days([Projected In Service Date] - [Interconnection Facilities ISD]) <= 30 then
"Yes"
else
"No"
else
null


---

✔ Why your formula failed

Power Query M doesn’t allow else (condition then … else …)

The correct structure is always:


if … then
if … then … else …
else
…

Also A <> "X" or "Y" is invalid.
Must be written as:


A = "X" or A = "Y"