Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Booz
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

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

View solution in original post

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

View solution in original post

13 REPLIES 13
Interkoubess
Solution Sage
Solution Sage

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 #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")))

@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.
Anonymous
Not applicable

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.