Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm trying to set up a custom column in power query as follows, and in result I am receiving a token error:
=if[Days from Open Conf Delivery Dt]=0 and <1, then "On-Time" else if [Days from Open Conf Delivery Dt]=>1 and <8,"Within 7 Days" else if [Days from Open Conf Delivery Dt]=>8 and <15,"8 - 14 Days" else if [Days from Open Conf Delivery Dt]=>15 and <30,"15 - 30 Days" else if [Days from Open Conf Delivery Dt]=>31 and <60,"30 - 60 Days" else if [Days from Open Conf Delivery Dt]=>61 and <90,"60 - 90 Days" else if [Days from Open Conf Delivery Dt]=>91 and <120,"90 - 120 Days" else if [Days from Open Conf Delivery Dt]=>121 and <150,"120 - 150 Days" else if [Days from Open Conf Delivery Dt]=>151 and <180,"150 - 180 Days" else if [Days from Open Conf Delivery Dt]=>180,"> 180 Days" else if [Days from Open Conf Delivery Dt]<0 and =>-7,"1 - 7 Days Early" else if [Days from Open Conf Delivery Dt]=>-8 and =>-15,"8 - 15 Days Early" else if [Days from Open Conf Delivery Dt]=>-8 and =>-15,"8 - 15 Days Early" else if [Days from Open Conf Delivery Dt]=>-16 and =>-30,"16 - 30 Days Early" else if [Days from Open Conf Delivery Dt]=>-31 and =>-45,"31 - 45 Days Early" else if [Days from Open Conf Delivery Dt]=>-46 and =>-60,"46 - 60 Days Early" else if [Days from Open Conf Delivery Dt]=>-61 and =>-80,"61 - 80 Days Early" else if [Days from Open Conf Delivery Dt]=>-81 and =>-120,"81 - 120 Days Early" else if [Days from Open Conf Delivery Dt]=>-121,"> 120 Days Early" else "Other"
Not sure where this is going wrong, will you please help me?
Solved! Go to Solution.
in theory this should work
if [Days from Open Conf Delivery Dt] = null then
"Unconfirmed"
else if [Days from Open Conf Delivery Dt] = 0 and [Days from Open Conf Delivery Dt] < 1 then
"On-Time"
else if [Days from Open Conf Delivery Dt] >= 1 and [Days from Open Conf Delivery Dt] < 8 then
"Within 7 Days"
else if [Days from Open Conf Delivery Dt] >= 8 and [Days from Open Conf Delivery Dt] < 15 then
"8 - 14 Days"
else if [Days from Open Conf Delivery Dt] >= 15 and [Days from Open Conf Delivery Dt] < 30 then
"15 - 30 Days"
else if [Days from Open Conf Delivery Dt] >= 31 and [Days from Open Conf Delivery Dt] < 60 then
"30 - 60 Days"
else if [Days from Open Conf Delivery Dt] >= 61 and [Days from Open Conf Delivery Dt] < 90 then
"60 - 90 Days"
else if [Days from Open Conf Delivery Dt] >= 91 and [Days from Open Conf Delivery Dt] < 120 then
"90 - 120 Days"
else if [Days from Open Conf Delivery Dt] >= 121 and [Days from Open Conf Delivery Dt] < 150 then
"120 - 150 Days"
else if [Days from Open Conf Delivery Dt] >= 151 and [Days from Open Conf Delivery Dt] < 180 then
"150 - 180 Days"
else if [Days from Open Conf Delivery Dt] >= 180 then
"> 180 Days"
else if [Days from Open Conf Delivery Dt] < 0 and [Days from Open Conf Delivery Dt] >= - 7 then
"1 - 7 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 8 and [Days from Open Conf Delivery Dt] >= - 15 then
"8 - 15 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 8 and [Days from Open Conf Delivery Dt] >= - 15 then
"8 - 15 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 16 and [Days from Open Conf Delivery Dt] >= - 30 then
"16 - 30 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 31 and [Days from Open Conf Delivery Dt] >= - 45 then
"31 - 45 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 46 and [Days from Open Conf Delivery Dt] >= - 60 then
"46 - 60 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 61 and [Days from Open Conf Delivery Dt] >= - 80 then
"61 - 80 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 81 and [Days from Open Conf Delivery Dt] >= - 120 then
"81 - 120 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 121 then
"> 120 Days Early"
else
"Other"
Proud to be a Super User!
😥 this code is very messy no wonder you can't figure out what is going on...
use power query formatter to format it, so its more readable
https://www.powerqueryformatter.com/formatter
firstly you dont use commas in power query in your if statement it is
if condition then result
else if condition then result
until...
else
example
if [Days from Open Conf Delivery Dt] = 0 then "On-Time"
else if [Days from Open Conf Delivery Dt] >= 1 and [Days from Open Conf Delivery Dt] < 8 then
"Within 7 Days"
else "NA"
another issue is its >= not =>
also you must declare the column each time
if [Days from Open Conf Delivery Dt] >= 1 and [Days from Open Conf Delivery Dt] < 8
Proud to be a Super User!
Hi,
I removed the common before then at the top where the error is indicated, but I cannot tell what is required instead...
I edited my post with more information 🙂
Proud to be a Super User!
Can you share the error here?
Have you tried removing the comma before 'then'
Please see highlighted for error:
have already listed what the problem is, you not telling it which field to compare it to.
you need to put the field condition in 2 x
else if [Days from Open Conf Delivery Dt] >= 1 and [Days from Open Conf Delivery Dt] < 8 then
you have
else if [Days from Open Conf Delivery Dt] >= 1 and < 8 then
Proud to be a Super User!
Yes i tried removing the comma before then and i tired removing then and using a comma either way i get this same error.
the problem is not only the comma, remove the comma yes, did you read my previous post before this?
Proud to be a Super User!
Hi, this seems to work for the first part of the formula, but not in the second step it says my "and" is an error... Please see highlighted...
try this
if [Days from Open Conf Delivery Dt] = 0 and [Days from Open Conf Delivery Dt] < 1 then
"On-Time"
else if [Days from Open Conf Delivery Dt] >= 1 and [Days from Open Conf Delivery Dt] < 8 then
"Within 7 Days"
else if [Days from Open Conf Delivery Dt] >= 8 and [Days from Open Conf Delivery Dt] < 15 then
"8 - 14 Days"
else if [Days from Open Conf Delivery Dt] >= 15 and [Days from Open Conf Delivery Dt] < 30 then
"15 - 30 Days"
else if [Days from Open Conf Delivery Dt] >= 31 and [Days from Open Conf Delivery Dt] < 60 then
"30 - 60 Days"
else if [Days from Open Conf Delivery Dt] >= 61 and [Days from Open Conf Delivery Dt] < 90 then
"60 - 90 Days"
else if [Days from Open Conf Delivery Dt] >= 91 and [Days from Open Conf Delivery Dt] < 120 then
"90 - 120 Days"
else if [Days from Open Conf Delivery Dt] >= 121 and [Days from Open Conf Delivery Dt] < 150 then
"120 - 150 Days"
else if [Days from Open Conf Delivery Dt] >= 151 and [Days from Open Conf Delivery Dt] < 180 then
"150 - 180 Days"
else if [Days from Open Conf Delivery Dt] >= 180 then
"> 180 Days"
else if [Days from Open Conf Delivery Dt] < 0 and [Days from Open Conf Delivery Dt] >= - 7 then
"1 - 7 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 8 and [Days from Open Conf Delivery Dt] >= - 15 then
"8 - 15 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 8 and [Days from Open Conf Delivery Dt] >= - 15 then
"8 - 15 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 16 and [Days from Open Conf Delivery Dt] >= - 30 then
"16 - 30 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 31 and [Days from Open Conf Delivery Dt] >= - 45 then
"31 - 45 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 46 and [Days from Open Conf Delivery Dt] >= - 60 then
"46 - 60 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 61 and [Days from Open Conf Delivery Dt] >= - 80 then
"61 - 80 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 81 and [Days from Open Conf Delivery Dt] >= - 120 then
"81 - 120 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 121 then
"> 120 Days Early"
else
"Other"
Proud to be a Super User!
You are the BEST! Do you also know how to handle nulls? where [Days from Open Conf Delivery Dt] is null I need it to say "Unconfirmed". Thank you so much!!
in theory this should work
if [Days from Open Conf Delivery Dt] = null then
"Unconfirmed"
else if [Days from Open Conf Delivery Dt] = 0 and [Days from Open Conf Delivery Dt] < 1 then
"On-Time"
else if [Days from Open Conf Delivery Dt] >= 1 and [Days from Open Conf Delivery Dt] < 8 then
"Within 7 Days"
else if [Days from Open Conf Delivery Dt] >= 8 and [Days from Open Conf Delivery Dt] < 15 then
"8 - 14 Days"
else if [Days from Open Conf Delivery Dt] >= 15 and [Days from Open Conf Delivery Dt] < 30 then
"15 - 30 Days"
else if [Days from Open Conf Delivery Dt] >= 31 and [Days from Open Conf Delivery Dt] < 60 then
"30 - 60 Days"
else if [Days from Open Conf Delivery Dt] >= 61 and [Days from Open Conf Delivery Dt] < 90 then
"60 - 90 Days"
else if [Days from Open Conf Delivery Dt] >= 91 and [Days from Open Conf Delivery Dt] < 120 then
"90 - 120 Days"
else if [Days from Open Conf Delivery Dt] >= 121 and [Days from Open Conf Delivery Dt] < 150 then
"120 - 150 Days"
else if [Days from Open Conf Delivery Dt] >= 151 and [Days from Open Conf Delivery Dt] < 180 then
"150 - 180 Days"
else if [Days from Open Conf Delivery Dt] >= 180 then
"> 180 Days"
else if [Days from Open Conf Delivery Dt] < 0 and [Days from Open Conf Delivery Dt] >= - 7 then
"1 - 7 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 8 and [Days from Open Conf Delivery Dt] >= - 15 then
"8 - 15 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 8 and [Days from Open Conf Delivery Dt] >= - 15 then
"8 - 15 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 16 and [Days from Open Conf Delivery Dt] >= - 30 then
"16 - 30 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 31 and [Days from Open Conf Delivery Dt] >= - 45 then
"31 - 45 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 46 and [Days from Open Conf Delivery Dt] >= - 60 then
"46 - 60 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 61 and [Days from Open Conf Delivery Dt] >= - 80 then
"61 - 80 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 81 and [Days from Open Conf Delivery Dt] >= - 120 then
"81 - 120 Days Early"
else if [Days from Open Conf Delivery Dt] >= - 121 then
"> 120 Days Early"
else
"Other"
Proud to be a Super User!
You made my day! Thank you!!!
well that's all good then!
Proud to be a Super User!