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

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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Ashish,

 

Isn't there a different formula "language" for PowerQuery?  The formula below is what is used inside of excel itself, but I'm trying to pulling the past 3 months of data prior to the data pulling into my exel sheet.

 

=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,

    There are 2 formula/coding languages in PBI desktop - Mashup or simple "M" used in the Query Editor and DAX used in PowerPivot (for writing measures).  My suggested formula is a DAX calculated column formula.