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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Good Afternoon!

 

Looks like you guys are having more success than myself with a similar problem... with very little PowerQuery knowledge, I'm getting lost in the syntax.  Any help you can offer up converting this excel formula below to the correct syntax would be greatly appreciated!

 

=IF(AND([@InvoiceDate]<=EOMONTH(TODAY(),-1),[@InvoiceDate]>=EOMONTH(TODAY(),-4)+1),[@ExtensionAmt],0)

 

Thank you,

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

    Would you be OK with a calculated column formula (DAX) solution?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Good Evening!

       

      Yes, I'm willing to give any solutions a try at this point.

       

      Thank you!

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

        Try this calculated column formula

        =IF(AND([InvoiceDate]<=EOMONTH(TODAY(),-1),[InvoiceDate]>=EOMONTH(TODAY(),-4)+1),[ExtensionAmt],0)

        Hope this helps.