Forum Discussion

Booz's avatar
Booz
Frequent Visitor
8 years ago
Solved

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]>=...
Need Help
  • Interkoubess's avatar
    Interkoubess
    8 years ago

    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's avatar
    Booz
    8 years ago

    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's avatar
Booz
Frequent Visitor
8 years ago

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's avatar
Interkoubess
Icon for Solution Sage rankSolution Sage
8 years ago

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's avatar
    Booz
    Frequent Visitor
    8 years ago

    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