cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: IF AND Statements in power query

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

Highlighted
Frequent Visitor

Re: IF AND Statements in power query

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
Highlighted
Solution Sage
Solution Sage

Re: IF AND Statements in power query

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

Highlighted
Frequent Visitor

Re: IF AND Statements in power query

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

Highlighted
Solution Sage
Solution Sage

Re: IF AND Statements in power query

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

Highlighted
Microsoft
Microsoft

Re: IF AND Statements in power query

@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.
Highlighted
Frequent Visitor

Re: IF AND Statements in power query

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

Highlighted
Frequent Visitor

Re: IF AND Statements in power query

Lydia,

 

I used PowerBI too, but for this partucular project we wanted on power query. Thanky you.

Highlighted
Anonymous
Not applicable

Re: IF AND Statements in power query

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?

Highlighted
New Member

Re: IF AND Statements in power query

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,

Highlighted
Super User IV
Super User IV

Re: IF AND Statements in power query

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/

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors