Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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]),""))
Solved! Go to 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.
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]
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.
This one worked too! Thank you for your help!
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
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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]
Check out the July 2025 Power BI update to learn about new features.