The Round menu option in Power Query does not round the same way that Excel rounds. This is a shorter video covering the same information that Excel MVP Celia Alves covered in episode 2391. In order to have Power Query ROUND and Excel ROUND return the same results, you have to edit the formula in Power Query to add RoundingModeAwayFromZero. This video shows you how.
Transcript of the video:
Learn Excel from MrExcel podcast episode 2396 How to ROUND in Power Query.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today is super fast version of episode 2392 and with my thanks to MVP Celia Alves.
How do we round in Excel versus Power Query?
So the round function Excel rounding to 0 decimal places always rounds away from zero when there's a tie.
So the 4.5 goes to five. 3.5 goes to four.
Negative 3.5 rounds away from zero to negative 4. That's what we expect.
If we take this through Power Query. Data, From table or Range.
And we just simply come here to Add Column and say that we want to do Rounding, Round….
To zero decimal places. Click OK.
We get the wrong results.
Power query rounds not away from zero, but towards the even number.
If you want to make this work, we're going to do Rounding, Round, to 0 decimal places. Click OK and then here in the formula bar.
If you don't see the formula bar, go to View, Formula Bar.
And then in the formula bar.
So after number dot round we're going to click after the zero decimal places. Put another comma and start typing R-O-U.
Go rounding mode away from zero. This isn't built into the dialog box yet.
It should be there's room. They could put Advanced there.
They could.
They could put a note that tell the Excel people that they're not going to get the exact same results that they expect from Excel.
Alright, so there's our Power Query Round Oh no. And our power query round fixed.
Alright, so the important thing here that the correct way to do that is with number dot round.
After the 0 decimal places adding the rounding mode dot away from zero.
Out of all those videos in this series, you would have thought that Round was the easy one.
That it would have just been a no brainer, but is not.
Hey check out my new book MrExcel 2021.
Click the I in the top right-hand corner for more information about that.
If you like these videos, please Like, Subscribe and Ring the Bell.
Feel free to post any questions down in YouTube comments below.
Hey, I want to thank for stopping by. We will see you next time for another cat from MrExcel.
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today is super fast version of episode 2392 and with my thanks to MVP Celia Alves.
How do we round in Excel versus Power Query?
So the round function Excel rounding to 0 decimal places always rounds away from zero when there's a tie.
So the 4.5 goes to five. 3.5 goes to four.
Negative 3.5 rounds away from zero to negative 4. That's what we expect.
If we take this through Power Query. Data, From table or Range.
And we just simply come here to Add Column and say that we want to do Rounding, Round….
To zero decimal places. Click OK.
We get the wrong results.
Power query rounds not away from zero, but towards the even number.
If you want to make this work, we're going to do Rounding, Round, to 0 decimal places. Click OK and then here in the formula bar.
If you don't see the formula bar, go to View, Formula Bar.
And then in the formula bar.
So after number dot round we're going to click after the zero decimal places. Put another comma and start typing R-O-U.
Go rounding mode away from zero. This isn't built into the dialog box yet.
It should be there's room. They could put Advanced there.
They could.
They could put a note that tell the Excel people that they're not going to get the exact same results that they expect from Excel.
Alright, so there's our Power Query Round Oh no. And our power query round fixed.
Alright, so the important thing here that the correct way to do that is with number dot round.
After the 0 decimal places adding the rounding mode dot away from zero.
Out of all those videos in this series, you would have thought that Round was the easy one.
That it would have just been a no brainer, but is not.
Hey check out my new book MrExcel 2021.
Click the I in the top right-hand corner for more information about that.
If you like these videos, please Like, Subscribe and Ring the Bell.
Feel free to post any questions down in YouTube comments below.
Hey, I want to thank for stopping by. We will see you next time for another cat from MrExcel.
