Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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")))
Solved! Go to Solution.
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
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
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
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 # | Line | QTY | ShipByDate | ShipDateByLine | OTDStatus |
10 | 1 | 1 | 3/3/2033 | 11/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")))
@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
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?
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,
Hi,
Would you be OK with a calculated column formula (DAX) solution?
Good Evening!
Yes, I'm willing to give any solutions a try at this point.
Thank you!
Hi,
Try this calculated column formula
=IF(AND([InvoiceDate]<=EOMONTH(TODAY(),-1),[InvoiceDate]>=EOMONTH(TODAY(),-4)+1),[ExtensionAmt],0)
Hope this helps.
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,
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.
Lydia,
I used PowerBI too, but for this partucular project we wanted on power query. Thanky you.
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
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