Forum Discussion

chel4782's avatar
chel4782
Frequent Visitor
6 years ago
Solved

Need Help with Conditional Column in Power BI-Power Query

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

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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's avatar
    chel4782
    6 years ago

    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]

4 Replies

  • SteveCampbell's avatar
    SteveCampbell
    Memorable Member
    6 years ago

    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's avatar
      chel4782
      Frequent Visitor
      6 years ago

      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]

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      chel4782
      Frequent Visitor
      6 years ago

      This one worked too!  Thank you for your help!