Need Help with Conditional Column in Power BI-Power Query

[chel4782]

I have these two statements in excel, but when I try to do in Power Query I keep getting errors.

1. =IF(IF([@[PT/NPT]]="PT",IF([@activity]="NONOPS","",[@DURATION]),IF(OR([@operation]="saftey",[@operation]="commute",[@operation]="lunch"),[@DURATION],""))="",0,IF([@[PT/NPT]]="PT",IF([@activity]="NONOPS","",[@DURATION]),IF(OR([@operation]="saftey",[@operation]="commute",[@operation]="lunch"),[@DURATION],"")))

2.=IF(IF([@[PT/NPT]]="NPT",IF(OR([@operation]="WOV_SDFN",[@operation]="WTHR",[@operation]="COMMUTE",[@operation]="LUNCH",[@operation]="SAFETY",[@operation]="SFTY MTG",[@operation]="WTHR/WIND",[@operation]="SFTY_ NPT", [@operation]="WOV_TRAV"),"",[@DURATION]),"")="",0,IF([@[PT/NPT]]="NPT",IF(OR([@operation]="WOV_SDFN",[@operation]="WTHR",[@operation]="COMMUTE",[@operation]="LUNCH",[@operation]="SAFETY",[@operation]="SFTY MTG",[@operation]="WTHR/WIND",[@operation]="SFTY_ NPT", [@operation]="WOV_TRAV"),"",[@DURATION]),""))

[v-juanli-msft · Accepted solution]

Hi @chel4782 

Formula1:

Power query

Creat two columns

Custom=
if [#"PT/NPT"] = "PT" 
  then 
     if [activity] = "NONOPS" then " " else [DURATION]       else 
     if [operation] = "saftey" or [operation] = "commute" or [operation] = "lunch" 
       then [DURATION] else " "

Dax in a calcualted column

b =
IF (
    IF (
        [PT/NPT] = "PT",
        IF (
            [activity] = "NONOPS",
            "",
            [DURATION]
        ),
        IF (
            [operation] = "saftey"
                || [operation] = "commute"
                || [operation] = "lunch",
            [DURATION],
            ""
        )
    ) = "",
    0,
    IF (
        [PT/NPT] = "PT",
        IF (
            [activity] = "NONOPS",
            "",
            [DURATION]
        ),
        IF (
            [operation] = "saftey"
                || [operation] = "commute"
                || [operation] = "lunch",
            [DURATION],
            ""
        )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft · Accepted solution]

Hi @chel4782 

Formula1:

Power query

Creat two columns

Custom=
if [#"PT/NPT"] = "PT" 
  then 
     if [activity] = "NONOPS" then " " else [DURATION]       else 
     if [operation] = "saftey" or [operation] = "commute" or [operation] = "lunch" 
       then [DURATION] else " "

Dax in a calcualted column

b =
IF (
    IF (
        [PT/NPT] = "PT",
        IF (
            [activity] = "NONOPS",
            "",
            [DURATION]
        ),
        IF (
            [operation] = "saftey"
                || [operation] = "commute"
                || [operation] = "lunch",
            [DURATION],
            ""
        )
    ) = "",
    0,
    IF (
        [PT/NPT] = "PT",
        IF (
            [activity] = "NONOPS",
            "",
            [DURATION]
        ),
        IF (
            [operation] = "saftey"
                || [operation] = "commute"
                || [operation] = "lunch",
            [DURATION],
            ""
        )
    )
)

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chel4782]

This one worked too!  Thank you for your help!

[SteveCampbell]

the syntax in Power Query is

if [CONDITION] then {A} else if [CONDITION 2] then {B} else {C}

to use OR statement, use a little case or / and between conditions

if [CONDITION1] or [CONDITION2] then {A} else {B}

if [CONDITION1] and [CONDITION2] then {A} else {B}

Alternatively, you can add a conditional column 

[chel4782 · Accepted solution]

This one worked

if[activity]="NONOPS" and [#"PT/NPT"]="PT" then "0"
else if ([operation]="safety" or [operation]="commute" or [operation]="lunch") then [DURATION] else [DURATION]