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)"