PowerQuery Rounding

[SOQLee]

How would you round the number 2.33 up to whole number 3 instead of the 2. Power Query's default for 0.33 is to round down and I want to round up.

Also, how would you wrap the ROUND function in a formula when adding the values of two columns.


[TABLE="width: 199"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Value[/TD]
[TD]RoundUp[/TD]
[TD]Default[/TD]
[/TR]
[TR]
[TD]2.33[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2.67[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3.33[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3.67[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4.00[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4.33[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4.67[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5.00[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5.33[/TD]
[TD]6[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]5.67[/TD]
[TD]6[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6.33[/TD]
[TD]7[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]6.67[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7.00[/TD]
[TD]7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]7.67[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]8.00[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[Ozeroth]

Hi SOQLee

In the interface, you can find the round-up function in the ribbon under Transform -> Number Column -> Rounding -> Round Up.
The function itself is Number.RoundUp.

You can use Number.RoundUp(...) within another formula, anywhere that excepts a number type. If you need more help, please post back more details on exactly what you want the formula to do.

Regards,
Owen

 

[SOQLee]

Hi Owen,

Thanks for the response. I did use the Round Up function on the ribbon...but was wondering how to write the RoundUp within another formula. For example, I have two custom columns and wondered how to write just one column which would combine the formulas wrapped in a RoundUp statement (thereby avoiding a few PoweQuery steps).

Custom1 = Value.Divide([EndValue]-[StartValue],12)
Custom2 = [EndYear]-[StartYear]

Custom3 = Custom1 + Custom2

 

[Ozeroth]

Sure, in that case you could create a custom column such as:

Custom3 = Number.RoundUp( Value.Divide([EndValue]-[StartValue],12) + [EndYear]-[StartYear] )

 

[SOQLee]

Excellent! I wasn't sure if I had written it correctly. I was getting errors...it was the data type that was throwing Custom3 off. Thank you.

Sure, in that case you could create a custom column such as:

Custom3 = Number.RoundUp( Value.Divide([EndValue]-[StartValue],12) + [EndYear]-[StartYear] )