Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
@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"
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.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k
Thanks for getting back to me, I've added the speech marks and the error is still there.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |