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.

Value	RoundUp	Default
2.33	3	2
2.67	3	3
3.00	3	3
3.33	4	3
3.67	4	4
4.00	4	4
4.33	5	4
4.67	5	5
5.00	5	5
5.33	6	5
5.67	6	6
6.33	7	6
6.67	7	7
7.00	7	7
7.67	8	8
8.00	8	8

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[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] )