Custom Column If Statement Power Query

[JaclynPugh2022]

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?

[vanessafvg]

😥 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

[JaclynPugh2022]

Hi,

I removed the common before then at the top where the error is indicated, but I cannot tell what is required instead...

[vanessafvg]

I edited my post with more information 🙂

[SwayamSinha]

Can you share the error here?

Have you tried removing the comma before 'then'

[JaclynPugh2022]

Please see highlighted for error:

[vanessafvg]

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

[JaclynPugh2022]

Yes i tried removing the comma before then and i tired removing then and using a comma either way i get this same error.

[vanessafvg]

the problem is not only the comma, remove the comma yes, did you read my previous post before this?   

[JaclynPugh2022]

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...

[vanessafvg]

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"

[JaclynPugh2022]

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!!

[vanessafvg · Accepted 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"

[JaclynPugh2022]

You made my day!  Thank you!!!

[vanessafvg]

well that's all good then!