Nested IF OR statement in Power Query

[Anonymous]

Hi,

Just wondering if someone could help with an error I'm getting from my Power Query IF / OR statement below which I am using to get times into a time bucket within a Custom Column.

I keep getting a  Token Then expected error message at the bottom and within the first line between the first double colon and the second set of 00s for example the red underline appears here 00:00:00

=if [#"ReceievedDate - Time"] => 00:00:00 or 
[#"ReceievedDate - Time"] =< 01:00:00 
then 00:00 - 01:00

else 
if [#"ReceievedDate - Time"] => 01:00:00 or 
[#"ReceievedDate - Time"] =< 02:00:00 
then 01:00 - 02:00

else 
if [#"ReceievedDate - Time"] => 02:00:00 or 
[#"ReceievedDate - Time"] =< 03:00:00 
then 02:00 - 03:00

else 
if [#"ReceievedDate - Time"] => 03:00:00 or 
[#"ReceievedDate - Time"] =< 04:00:00 
then 03:00 - 04:00

else 
if [#"ReceievedDate - Time"] => 04:00:00 or 
[#"ReceievedDate - Time"] =< 05:00:00 
then 04:00 - 05:00

else 
if [#"ReceievedDate - Time"] => 05:00:00 or 
[#"ReceievedDate - Time"] =< 06:00:00 
then 05:00 - 06:00

else 
if [#"ReceievedDate - Time"] => 06:00:00 or 
[#"ReceievedDate - Time"] =< 07:00:00 
then 06:00 - 07:00

else 
if [#"ReceievedDate - Time"] => 07:00:00 or 
[#"ReceievedDate - Time"] =< 08:00:00 
then 07:00 - 08:00

else 
if [#"ReceievedDate - Time"] => 08:00:00 or 
[#"ReceievedDate - Time"] =< 09:00:00 
then 08:00 - 09:00

else 
if [#"ReceievedDate - Time"] => 09:00:00 or 
[#"ReceievedDate - Time"] =< 10:00:00 
then 09:00 - 10:00

else 
if [#"ReceievedDate - Time"] => 10:00:00 or 
[#"ReceievedDate - Time"] =< 11:00:00 
then 10:00 - 11:00

else 
if [#"ReceievedDate - Time"] => 11:00:00 or 
[#"ReceievedDate - Time"] =< 12:00:00 
then 11:00 - 12:00

else 
if [#"ReceievedDate - Time"] => 12:00:00 or 
[#"ReceievedDate - Time"] =< 13:00:00 
then 12:00 - 13:00

else 
if [#"ReceievedDate - Time"] => 13:00:00 or 
[#"ReceievedDate - Time"] =< 14:00:00 
then 13:00 - 14:00

else 
if [#"ReceievedDate - Time"] => 14:00:00 or 
[#"ReceievedDate - Time"] =< 15:00:00 
then 14:00 - 15:00

else 
if [#"ReceievedDate - Time"] => 15:00:00 or 
[#"ReceievedDate - Time"] =< 16:00:00 
then 15:00 - 16:00

else 
if [#"ReceievedDate - Time"] => 16:00:00 or 
[#"ReceievedDate - Time"] =< 17:00:00 
then 16:00 - 17:00

else 
if [#"ReceievedDate - Time"] => 17:00:00 or 
[#"ReceievedDate - Time"] =< 18:00:00 
then 17:00 - 18:00

else 
if [#"ReceievedDate - Time"] => 18:00:00 or 
[#"ReceievedDate - Time"] =< 19:00:00 
then 18:00 - 19:00

else 
if [#"ReceievedDate - Time"] => 19:00:00 or 
[#"ReceievedDate - Time"] =< 20:00:00 
then 19:00 - 20:00

else 
if [#"ReceievedDate - Time"] => 20:00:00 or 
[#"ReceievedDate - Time"] =< 21:00:00 
then 20:00 - 21:00

else 
if [#"ReceievedDate - Time"] => 21:00:00 or 
[#"ReceievedDate - Time"] =< 22:00:00 
then 21:00 - 22:00

else 
if [#"ReceievedDate - Time"] => 22:00:00 or 
[#"ReceievedDate - Time"] =< 23:00:00 
then 22:00 - 23:00

else 23:00 - 00:00

Many thanks

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

Your greater signs and equal signs are not in the correct position, and you did not add double quotes to the fields after Then function.

So you can refer this following formula,

if [#"ReceievedDate - Time"] >= #time (0,0,0) or 
[#"ReceievedDate - Time"] < #time  (1,0,0) 
then "00:00 - 01:00"

If you have any question, please kindly ask here and we will try to resolve it.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dhreorg]

Hi, I was looking to get help with an IF statement error that I'm getting with creating a new calculated column

if ([Sales Amount] > 0 and [Product] = "Product 1" or [Product] = "Product 2" or [Product] = "Product 3"),1,0)

I'm essentially trying to create multiple columns, where I can group various products together into packages, under specific parameteres.

but I keep getting an error;

Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly.

Would appreciate any advice on this.

[parry2k]

@dhreorg try this

if [Sales Amount] > 0 and ([Product] = "Product 1" or [Product] = "Product 2" or [Product] = "Product 3") then 1 else 0

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dhreorg]

@parry2k  thank you, that worked perfect! 

[Anonymous]

Hi @v-zhenbw-msft ,

This is works perfectly thank you.

[Anonymous]

Hi @parry2k @az38 

Ok so I've amended it to this, and it still comes back with the Token Then required error highlighting the "ti" in the first row of the word time.

Could either of you kindly script out how this should be written exactly using the first paragraph of my script? I'm getting something wrong somewhere and I just can't see it.

=if [#"ReceievedDate - Time"] => #time (0,0,0) or 
[#"ReceievedDate - Time"] =< #time  (1,0,0) 
then 00:00 - 01:00

else 
if [#"ReceievedDate - Time"] => #time  (1,0,0)  or 
[#"ReceievedDate - Time"] =< #time (2,0,0)  
then 01:00 - 02:00

else 
if [#"ReceievedDate - Time"] => #time  (2,0,0)  or 
[#"ReceievedDate - Time"] =< #time (3,0,0)  
then 02:00 - 03:00

else 
if [#"ReceievedDate - Time"] => #time (3,0,0) or 
[#"ReceievedDate - Time"] =< #time  (4,0,0) 
then 03:00 - 04:00

else 
if [#"ReceievedDate - Time"] => #time  (4,0,0)  or 
[#"ReceievedDate - Time"] =< #time (5,0,0) 
then 04:00 - 05:00

else 
if [#"ReceievedDate - Time"] => #time  (5,0,0)  or 
[#"ReceievedDate - Time"] =< #time  (6,0,0)  
then 05:00 - 06:00

else 
if [#"ReceievedDate - Time"] => #time  (6,0,0) or 
[#"ReceievedDate - Time"] =< #time  (7,0,0) 
then 06:00 - 07:00

else 
if [#"ReceievedDate - Time"] => #time  (7,0,0)  or 
[#"ReceievedDate - Time"] =< #time  (8,0,0) 
then 07:00 - 08:00

else 
if [#"ReceievedDate - Time"] => #time  (8,0,0)  or 
[#"ReceievedDate - Time"] =< #time  (9,0,0) 
then 08:00 - 09:00

else 
if [#"ReceievedDate - Time"] => #time  (9,0,0)  or 
[#"ReceievedDate - Time"] =< #time  (10,0,0) 
then 09:00 - 10:00

else 
if [#"ReceievedDate - Time"] => #time (10,0,0) or 
[#"ReceievedDate - Time"] =< #time (11,0,0)  
then 10:00 - 11:00

else 
if [#"ReceievedDate - Time"] => #time (11,0,0)  or 
[#"ReceievedDate - Time"] =< #time (12,0,0)  
then 11:00 - 12:00

else 
if [#"ReceievedDate - Time"] => #time (12,0,0)   or 
[#"ReceievedDate - Time"] =< #time (13,0,0)  
then 12:00 - 13:00

else 
if [#"ReceievedDate - Time"] => #time (13,0,0)   or 
[#"ReceievedDate - Time"] =< #time (14,0,0)  
then 13:00 - 14:00

else 
if [#"ReceievedDate - Time"] => #time (14,0,0)   or 
[#"ReceievedDate - Time"] =< #time (15,0,0)  
then 14:00 - 15:00

else 
if [#"ReceievedDate - Time"] => #time (15,0,0)   or 
[#"ReceievedDate - Time"] =< #time (16,0,0)  
then 15:00 - 16:00

else 
if [#"ReceievedDate - Time"] => #time (16,0,0)   or 
[#"ReceievedDate - Time"] =< #time (17,0,0)  
then 16:00 - 17:00

else 
if [#"ReceievedDate - Time"] => #time (17,0,0)   or 
[#"ReceievedDate - Time"] =< #time (18,0,0)  
then 17:00 - 18:00

else 
if [#"ReceievedDate - Time"] => #time (18,0,0)   or 
[#"ReceievedDate - Time"] =< #time (19,0,0)  
then 18:00 - 19:00

else 
if [#"ReceievedDate - Time"] => (19,0,0)   or 
[#"ReceievedDate - Time"] =< (20,0,0)  
then 19:00 - 20:00

else 
if [#"ReceievedDate - Time"] => (20,0,0)   or 
[#"ReceievedDate - Time"] =< (21,0,0)   
then 20:00 - 21:00

else 
if [#"ReceievedDate - Time"] => (21,0,0)   or 
[#"ReceievedDate - Time"] =< (22,0,0)   
then 21:00 - 22:00

else 
if [#"ReceievedDate - Time"] => (22,0,0)   or 
[#"ReceievedDate - Time"] =< (23,0,0)  
then 22:00 - 23:00

else 23:00 - 00:00

 Many Thanks

[az38]

@Anonymous 

i'm not sure in your names. in marks "=<", in spaces after "time" and in then-statement. try like

=if [ReceievedDate - Time] => #time(0,0,0) or 
[ReceievedDate - Time] =< #time(1,0,0) 
then "00:00 - 01:00"

[az38]

Hi @Anonymous 

try to use #time

if [#"ReceievedDate - Time"] >= #time(0, 0, 0) then...

[Anonymous]

Hi @az38 

Thanks for getting back to me, this doesn't work I get the red under line under the "ti" of the word time in the first line.

[parry2k]

@Anonymous you should have it in quotes:

=if [#"ReceievedDate - Time"] => 00:00:00 or 
[#"ReceievedDate - Time"] =< 01:00:00 
then "00:00 - 01:00"

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k 

Thanks for getting back to me, I've added the speech marks and the error is still there.

[parry2k]

@Anonymous yes you also have to use a time function as mentioned by @az38 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[parry2k]

@Anonymous it should work, read here

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡