Forum Discussion

MichaelK27's avatar
MichaelK27
New Member
3 years ago
Solved

Power query 'Token Else Expected' error

Hi,

 

I'm relatively new to Power Query and new to this forum. I'm hoping somebody can help me with my issue.

 

I'm trying to create a custom column where I'm trying to define a (in Excel) ceiling formula, but I get an error. I don't know what I need to do, because my formula should be complete, but I can't implement it.

 

The formula I created is as follow:

if [#"Weight (kg)"] > 200 then Number.RoundUp([#"Weight (kg)"]/50,0)*50 else
if [#"Weight (kg)"] > 100 then Number.RoundUp([#"Weight (kg)"]/20,0)*20 else
if [#"Weight (kg)"] > 30 then Number.RoundUp([#"Weight (kg)"]/10,0)*10 else
if [#"Weight (kg)"] > 0 then Number.RoundUp([#"Weight (kg)"]/5,0)*5

 

This is my Excel formula:

=IF(V2="";"";IF(V2>200;CEILING(V2;50);IF(V2>100;CEILING(V2;20);IF(V2>=30;CEILING(V2;10);CEILING(V2;5)))))

 

V2 corresponds with the [#"Weight (kg)"] field.

 

Hope somebody can help me with this.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi MichaelK27 ,

    There isn't else part in the end of the formula. For each if statement, there should be then and else.

    If the [#"Weight (kg)"] <=0, what result do you want, directly add the result in the else part in the end.

     

    Or based on the condition in your Excel formula:

    This is my Excel formula:

    =IF(V2="";"";IF(V2>200;CEILING(V2;50);IF(V2>100;CEILING(V2;20);IF(V2>=30;CEILING(V2;10);CEILING(V2;5)))))

    I write a corresponding code:

    if [#"Weight (kg)"]="" then "" else
if [#"Weight (kg)"] > 200 then Number.RoundUp([#"Weight (kg)"]/50,0)*50 else
if [#"Weight (kg)"] > 100 then Number.RoundUp([#"Weight (kg)"]/20,0)*20 else
if [#"Weight (kg)"] > 30 then Number.RoundUp([#"Weight (kg)"]/10,0)*10 else
 Number.RoundUp([#"Weight (kg)"]/5,0)*5

    Best Regards,
    Community Support Team _ kalyj

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

4 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi MichaelK27 ,

    There isn't else part in the end of the formula. For each if statement, there should be then and else.

    If the [#"Weight (kg)"] <=0, what result do you want, directly add the result in the else part in the end.

     

    Or based on the condition in your Excel formula:

    This is my Excel formula:

    =IF(V2="";"";IF(V2>200;CEILING(V2;50);IF(V2>100;CEILING(V2;20);IF(V2>=30;CEILING(V2;10);CEILING(V2;5)))))

    I write a corresponding code:

    if [#"Weight (kg)"]="" then "" else
if [#"Weight (kg)"] > 200 then Number.RoundUp([#"Weight (kg)"]/50,0)*50 else
if [#"Weight (kg)"] > 100 then Number.RoundUp([#"Weight (kg)"]/20,0)*20 else
if [#"Weight (kg)"] > 30 then Number.RoundUp([#"Weight (kg)"]/10,0)*10 else
 Number.RoundUp([#"Weight (kg)"]/5,0)*5

    Best Regards,
    Community Support Team _ kalyj

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