Power query 'Token Else Expected' error

[MichaelK27]

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.

[v-yanjiang-msft · Accepted solution]

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.

[MichaelK27]

Hi @v-yanjiang-msft ,

That makes sense. Thank you!

[MichaelK27]

Hi @ImkeF ,

The error message is this:

[ImkeF]

Hi @MichaelK27 ,
what does the error-message say?