Search code examples
if-statementconditional-statementspowerquerym

PowerQuery if with multiple conditions AND OR

How can I replicate this on PowerQuery ??

if(ATC[WorkitemAssetState]=64,
if(ATC[WorkitemIsClosed]=FALSE,
if(ATC[WorkitemIsDeleted]=FALSE,
if(ATC[M_WorkitemStatus]IN{"Reviewing","Available","Research","Progressing","Testing","Resolved","<none>"},
if(ATC[M_DefectResolutionId]IN {"-","<Unknown>","Resolution:1","Resolution:7"},1,-1)
,0)
,0)
,0)
,0)

I have done the following, but it does not seem to work properly:

if [WorkitemAssetState]=64 and 
[WorkitemIsClosed]="FALSE" and
[WorkitemIsDeleted]="FALSE" and
[M_WorkitemStatus]="Reviewing" or [M_WorkitemStatus]= "Available" or [M_WorkitemStatus]= "Research" or [M_WorkitemStatus] = "Progressing" or [M_WorkitemStatus] = "Testing" or [M_WorkitemStatus] = "Resolved" or [M_WorkitemStatus] = "<none>" or [M_WorkitemStatus] = "<none>" or [M_WorkitemStatus] = "-" and
[M_DefectResolutionId]= "-" or [M_DefectResolutionId] = "<Unknown>" or [M_DefectResolutionId] = "Resolution:1" or[M_DefectResolutionId] = "Resolution:7" then 1 else 0

I am pretty sure it's because of the multiple OR statements.
I don't think it's because of the FALSE's, btw.

Thanks!!

Solution

  • If you are not sure about the priorities of "and" and "or", it's better to use parentheses.

    In fact, "and"'s are evaluated first, then "or"'s. So "a and b or c and d" is equivalent with "(a and b) or (c and d)". Not with "a and (b or c) and d", which - I understand - you are looking for. If so, you need to add parentheses around the collections of "or"'s.

    Assuming you have logical values, then "FALSE" won't work: you need false without quotes and in all lower case.

    By the way "does not seem to work properly" is not exactly the preferred way to explain what is going wrong. I guess you get (almost) all 1's and (almost) no 0's?