Excel offers the cool MROUND function to round to the nearest 5 or 25 or any number. There is no built-in equivalent function in Power Query's M code. However, using a formula such as =Number.Round([Number]/25,0,RoundMode.AwayFromZero)*25 you can do the same thing.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2393.
How can we MROUND in Power Query?
Welcome back to the MrExcel net cast.
I am Bill Jelen. Excel has a lot of ways to round and one of the cool ways is MROUND which rounds to a certain multiple.
MROUND of A2 comma 25 will round to the nearest 25.
But there's always been a problem with MROUND, especially for negative numbers.
It doesn't want to work for negative numbers.
You have to trick it.
You would have to say the MROUND of A13 comma minus 25.
So if I know that 25 is what I want to round to, I multiply 25 by the SIGN of A2.
SIGN is either 1 for positive or -1 for negative.
That makes sure that down here we're rounding to the nearest 25.
Now the hassle: Power Query doesn't have MROUND, but that's OK.
We can actually do it just with straight Power Query functions.
It's not built into the user interface, but it's pretty simple to do.
I have a table over here.
Data from a table or range.
And I want to add a new column based on Number.
So under Add Column, a custom column.
Now let's call it PQ for Power Query MROUND the M round function.
We are going to use Number.Round, of our number.
Alright, but now here's the thing.
In MROUND we get to specify what we are going to round to… the nearest 25.
So whatever that number is, divided by 25.
Comma 0 decimal places.
And then for rounding modes.
I'll start typing rounding mode and choose away from zero rounding mode away from zero.
Close that and then finally, because we already divided by 25, we now have to multiply by 25.
Alright, so the hassle for you: in Excel’s MROUND you're just specifying 25 once, but here in Power Query whatever number you put there, you're going to have to put it there as well.
Click OK and see how it looks.
So 65 rounds to 75 in Excel and rounds the 75 here.
25, 50, 50, 25.
Let's check the negative ones.
Oh they just work straight away without that crazy SIGN function right?
So right there.
Number dot round.
The original number divided by whatever you want it to round to.
Comma, 0 decimal places then rounding mode away from zero.
That's secret setting in there, and then finally multiplied by 25 and you should be good to go.
Hey check out my new book MrExcel 2021, Unmasking Excel.
Click the “I” in the top right hand corner For more information about that.
If you like this video please like subscribe and ring that bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
How can we MROUND in Power Query?
Welcome back to the MrExcel net cast.
I am Bill Jelen. Excel has a lot of ways to round and one of the cool ways is MROUND which rounds to a certain multiple.
MROUND of A2 comma 25 will round to the nearest 25.
But there's always been a problem with MROUND, especially for negative numbers.
It doesn't want to work for negative numbers.
You have to trick it.
You would have to say the MROUND of A13 comma minus 25.
So if I know that 25 is what I want to round to, I multiply 25 by the SIGN of A2.
SIGN is either 1 for positive or -1 for negative.
That makes sure that down here we're rounding to the nearest 25.
Now the hassle: Power Query doesn't have MROUND, but that's OK.
We can actually do it just with straight Power Query functions.
It's not built into the user interface, but it's pretty simple to do.
I have a table over here.
Data from a table or range.
And I want to add a new column based on Number.
So under Add Column, a custom column.
Now let's call it PQ for Power Query MROUND the M round function.
We are going to use Number.Round, of our number.
Alright, but now here's the thing.
In MROUND we get to specify what we are going to round to… the nearest 25.
So whatever that number is, divided by 25.
Comma 0 decimal places.
And then for rounding modes.
I'll start typing rounding mode and choose away from zero rounding mode away from zero.
Close that and then finally, because we already divided by 25, we now have to multiply by 25.
Alright, so the hassle for you: in Excel’s MROUND you're just specifying 25 once, but here in Power Query whatever number you put there, you're going to have to put it there as well.
Click OK and see how it looks.
So 65 rounds to 75 in Excel and rounds the 75 here.
25, 50, 50, 25.
Let's check the negative ones.
Oh they just work straight away without that crazy SIGN function right?
So right there.
Number dot round.
The original number divided by whatever you want it to round to.
Comma, 0 decimal places then rounding mode away from zero.
That's secret setting in there, and then finally multiplied by 25 and you should be good to go.
Hey check out my new book MrExcel 2021, Unmasking Excel.
Click the “I” in the top right hand corner For more information about that.
If you like this video please like subscribe and ring that bell.
Feel free to post any questions or comments down in the YouTube comments below.
I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
