cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luiscb Regular Visitor
Regular Visitor

PowerQuery if with multiple conditions AND OR

Hello Guys,

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 its because of the multiple OR statements, dont think its because of "FALSE" btw.

Thanks!!


1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: PowerQuery if with multiple conditions AND OR

Hi @luiscb,

 

Based on my test, the formula below should work in PowerQuery. Smiley Happy

 

=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] = "" or [M_WorkitemStatus] = "-" )and
([M_DefectResolutionId]= "-" or [M_DefectResolutionId] = "<Unknown>" or [M_DefectResolutionId] = "Resolution:1" or[M_DefectResolutionId] = "Resolution:7") then 1 else 0

 

Regards

3 REPLIES 3
v-ljerr-msft Super Contributor
Super Contributor

Re: PowerQuery if with multiple conditions AND OR

Hi @luiscb,

 

Based on my test, the formula below should work in PowerQuery. Smiley Happy

 

=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] = "" or [M_WorkitemStatus] = "-" )and
([M_DefectResolutionId]= "-" or [M_DefectResolutionId] = "<Unknown>" or [M_DefectResolutionId] = "Resolution:1" or[M_DefectResolutionId] = "Resolution:7") then 1 else 0

 

Regards

luiscb Regular Visitor
Regular Visitor

Re: PowerQuery if with multiple conditions AND OR

Thank you, I managed in the end. Thank you! Smiley Happy

Technowolf Frequent Visitor
Frequent Visitor

Re: PowerQuery if with multiple conditions AND OR

Hi

How did you solve this, I still have the same issue,
I syntax is excatly the same , the moment I add the or conditions the and conditions stops to work.