Forum Discussion
Nested IF OR statement in Power Query
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
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.
13 Replies
- az38
Community Champion
Hi Anonymous
try to use #time
if [#"ReceievedDate - Time"] >= #time(0, 0, 0) then...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.
v-zhenbw-msftCommunity Support
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.
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
Super User
dhreorg try this
if [Sales Amount] > 0 and ([Product] = "Product 1" or [Product] = "Product 2" or [Product] = "Product 3") then 1 else 0I 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 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.⚡
- 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.⚡
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:00Many 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"
