cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Booz Frequent Visitor
Frequent Visitor

IF AND Statements in power query

I'm using power query and I would like to use IF & AND statement for the below formula that I had in Excel.

 

=IF(AND([@ShipByDate]<TODAY(),[@ShipDateByLine]=""),"Pending - late",IF([@ShipByDate]>=[@ShipDateByLine],"Shipped - On Time",IF(AND([@ShipByDate]>=TODAY(),[@ShipDateByLine]=""),"Pending - On Time","Shipped - Late")))

2 ACCEPTED SOLUTIONS

Accepted Solutions
Interkoubess Established Member
Established Member

Re: IF AND Statements in power query

Hi @Booz,

 

Please use this following formula in a calculated column and let me know if it does not work....

 

if List.AllTrue({[ShipByDate]<DateTime.Date(DateTime.LocalNow()),[ShipByDate]=null}) then "Pending - late" else if List.AllTrue({[ShipByDate]>=[ShipDateByLine]}) then "Shipped - On Time" else if List.AllTrue({[ShipByDate]>=DateTime.Date(DateTime.LocalNow()),[ShipByDate]=null}) then "Pending - On Time" else "Shipped - Late"

Ninter

Booz Frequent Visitor
Frequent Visitor

Re: IF AND Statements in power query

Thank you for the solution Interkoubess.

 

There is a small detail with the formula I corrected the names on the columns (ShipDateByline) in the fomula and now there is only one last thing the "Pending on Time" is not poping up in the results I checked the formula several times but no solution yet for that part, any comments on this one? The rest of the formula works fine.

 

if List.AllTrue({[ShipByDate]<DateTime.Date(DateTime.LocalNow()),[ShipDateByLine]=null}) then "Pending - late" else if List.AllTrue({[ShipByDate]>=[ShipDateByLine]}) then "Shipped - On Time" else if List.AllTrue({[ShipByDate]>=DateTime.Date(DateTime.LocalNow()),[ShipDateByLine]=null}) then "Pending - On Time" else "Shipped - Late"

 

Thank you so much

7 REPLIES 7
Interkoubess Established Member
Established Member

Re: IF AND Statements in power query

Hi @Boozm

 

Could you please share a sample data then I will make some try on it,

 

Anyway you can use List.AllTrue in order to retrieve your excel outcome.

 

Ninter

Booz Frequent Visitor
Frequent Visitor

Re: IF AND Statements in power query

Interkoubess,

 

Thanks for the reply, I will try List.AllTrue

 

About the sample data see below, basically I want to call on time delivery for 2 specific date columns one column ShipByDate (2033) and ShipDateByLine (2016).  Thanks for the help

 

SO #LineQTYShipByDateShipDateByLineOTDStatus
10113/3/203311/16/2016 

 

=IF(AND([@ShipByDate]<TODAY(),[@ShipDateByLine]=""),"Pending - late",IF([@ShipByDate]>=[@ShipDateByLine],"Shipped - On Time",IF(AND([@ShipByDate]>=TODAY(),[@ShipDateByLine]=""),"Pending - On Time","Shipped - Late")))

Interkoubess Established Member
Established Member

Re: IF AND Statements in power query

Hi @Booz,

 

Please use this following formula in a calculated column and let me know if it does not work....

 

if List.AllTrue({[ShipByDate]<DateTime.Date(DateTime.LocalNow()),[ShipByDate]=null}) then "Pending - late" else if List.AllTrue({[ShipByDate]>=[ShipDateByLine]}) then "Shipped - On Time" else if List.AllTrue({[ShipByDate]>=DateTime.Date(DateTime.LocalNow()),[ShipByDate]=null}) then "Pending - On Time" else "Shipped - Late"

Ninter

Moderator v-yuezhe-msft
Moderator

Re: IF AND Statements in power query

@Booz,

Do you persist to use Power Query? In Power BI Desktop, you are able to create a calculate column using the formula below.

Column=IF(AND(Table[ShipByDate]<TODAY(),Table[ShipDateByLine]=""),"Pending - late",IF(Table[ShipByDate]>=Table[ShipDateByLine],"Shipped - On Time",IF(AND(Table[ShipByDate]>=TODAY(),Table[ShipDateByLine]=""),"Pending - On Time","Shipped - Late")))



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Booz Frequent Visitor
Frequent Visitor

Re: IF AND Statements in power query

Thank you for the solution Interkoubess.

 

There is a small detail with the formula I corrected the names on the columns (ShipDateByline) in the fomula and now there is only one last thing the "Pending on Time" is not poping up in the results I checked the formula several times but no solution yet for that part, any comments on this one? The rest of the formula works fine.

 

if List.AllTrue({[ShipByDate]<DateTime.Date(DateTime.LocalNow()),[ShipDateByLine]=null}) then "Pending - late" else if List.AllTrue({[ShipByDate]>=[ShipDateByLine]}) then "Shipped - On Time" else if List.AllTrue({[ShipByDate]>=DateTime.Date(DateTime.LocalNow()),[ShipDateByLine]=null}) then "Pending - On Time" else "Shipped - Late"

 

Thank you so much

Booz Frequent Visitor
Frequent Visitor

Re: IF AND Statements in power query

Lydia,

 

I used PowerBI too, but for this partucular project we wanted on power query. Thanky you.

a297410 Visitor
Visitor

Re: IF AND Statements in power query

i'm tying to check if the value in a colum is greater then 8 m and less than 8:15 am. Can you please help me with a formula for that?