Dear All,
Does PQ have an equivalent of Excel's MROUND formula?
I need to round something to the nearest 0.5 and I cannot figure out how.
Thank you,
Blanka
Hi Blanka,
You can use Number.Round for this. e.g.
=Number.Round([value column], 1,RoundingMode.Up)
Or for rounding down:
=Number.Round([value column], 1, RoundingMode.Down)
More examples and rounding functions available at the link above.
Mynda
Hi Mynda,
Thank you for your idea.
I did check out the rounding functions that you had mentioned. However, I need to round to the nearest 0.5, so sometimes it would be RoundingMode.Up and others RoundingMode.Down.
I may need to figure out some kind of logic which would determine which method to use on a case by case basis. I just don't have any ideas yet.
Thank you again.
Blanka
Hi Blanka,
How about this Power Query formula:
= if
[Value] - Number.IntegerDivide([Value],1) < 0.25
then Number.RoundDown([Value],0)
else if
[Value] - Number.IntegerDivide([Value],1) > 0.25
and
[Value] - Number.IntegerDivide([Value],1) < 0.75
then (Number.RoundDown([Value],0)+0.50)
else if
[Value] - Number.IntegerDivide([Value],1) > 0.75
then Number.RoundUp([Value],0)
Mynda
Many thanks, Mynda.
This works like a charm.
I made a tiny modification; I added >=0.25 and >=0.75, instead of just >0.25 and >0.75 as this way it was rounding to 0.25 in some cases.
I entered:
= if
[Value] - Number.IntegerDivide([Value],1) < 0.25
then Number.RoundDown([Value],0)
else if
[Value] - Number.IntegerDivide([Value],1) >= 0.25
and
[Value] - Number.IntegerDivide([Value],1) < 0.75
then (Number.RoundDown([Value],0)+0.50)
else if
[Value] - Number.IntegerDivide([Value],1) >= 0.75
then Number.RoundUp([Value],0)
Thank you again, I appreciate your help.
Blanka
Tip 2: There are some another Round Example you can use also if need:
Example 1: Round to nearest million with one decimal point
(312789123 to 312.8)
=ROUND(A1/1000000,1)
This formula divides the number by million (1,000,000) and then round this to 1 decimal point.
Example 2: Get next even number
(42.1 to 44)
=EVEN(A1)
Gets you the next EVEN number (away from zero) 44.
Example 3: Get next odd number
(44.93 to 45)
=ODD(A1)
Gets you the next ODD number (away from zero) 45.
Example 4: Round to next 9 (i.e 19,29,39 etc.)
(23 to 29)
=ROUNDUP(A1,-1)-1
To do this, we just round up the number to next 10 and then subtract 1 from it.
Hope you liked it. Comment your take on the ROUNDUP Function in Excel.
Hi Darshan,
Thanks for sharing, but this question is about rounding in Power Query using M functions. Those are Excel round functions.
Mynda
Hi Mynda,
I cannot apply your PQ Formula wrote on October 1, 2017 to my query. My problem basically the same, to rounding up to the nearest 0.0005, so i can lookup to the lookup table. In excel maybe I can use CEILING function to the nearest 0.0005.
Kindly need your formula to write in my query, I attached the excel file.
Thank you.
Chandra
Hi Chandra,
Number.Round has multiple rounding modes, have you tried them all? Here is a link to the resource, you have multiple examples on that page.
Hi Catalin,
thanks for the link, I try to apply the Number.Round based on that examples.
Thank You,
Chandra
Try using the custom formula "Number.Round(Number.Round([Column]*2,0)/2,1)"