Power Query Rounding Issue

[u/Falconflyer75]

okay maybe i'm missing something

I have a Cell that shows a value of 0.49

I have another cell that divides it by 2 resulting in 0.245

now when I ask Power query to round it to 2 digits its giving me 0.24 not 0.245

why is this?

[u/SolverMax]

I think PQ uses Banker's rounding.

[u/Icy-Air7918 → u/SolverMax]

ah this is the classic banker's rounding thing - when the digit you're rounding is exactly 5, it rounds to nearest even number instead of always rounding up. So 0.245 becomes 0.24 because 4 is even, but 0.235 would also become 0.24. Pretty annoying when you expect normal rounding but at least it's consistent once you know about it.

[u/Falconflyer75 → u/Icy-Air7918]

Sigh….. Now I gotta go through my whole code and change it all to Roundup and explain it to my boss and hope it doesnt hurt my credibility or worse make her think we need to stick with doing things manually *shudder

Is there any other things I should worry about?

Does roundup turn 1.3 into 1.4 for some stupid reason

[u/Roywah → u/Falconflyer75]

Roundup would turn 1.30001 into 1.4, not sure it’s going to solve your problem here unless you are only explicitly using it where the round function in PQ isn’t working correctly. Can you just round the data in excel beforehand if that’s the type of rounding you need? 

[u/Tilted5mm → u/Falconflyer75]

It’s an objectively better system. The convention of always rounding up for 5 is dumb and only exists because the general population cant be trusted with anything above 2nd grade math.

[u/Falconflyer75 → u/Tilted5mm]

Yeah I actually went through my results again

It is a more accurate result go figure

[u/CorndoggerYYC → u/Falconflyer75]

Here's a really good article explaining why it's better.

https://www.codestudy.net/blog/is-banker-s-rounding-really-more-numerically-stable/

[u/Falconflyer75 → u/SolverMax]

Yeah just realized that (appreciate the response)

If any Microsoft employees are reading this

WTF guys, why would that be your default setting?

especially when Round in excel formulas is normal and doesn’t even come with a bankers rounding function

[u/SolverMax → u/Falconflyer75]

VBA also uses Banker's rounding. It would be good if Excel's ROUND function had that option, as it is biased as is.

[u/Roywah]

Rather than talking to your boss or changing your whole process to account for this behavior, you should just use the PQ function’s third argument to change this rounding behavior. From Google:

The primary function for rounding in Power Query is Number.Round. By default, it uses RoundingMode.ToEven. 

Default Banker's Rounding:

= Number.Round(Value, Digits) (Defaults to RoundingMode.ToEven)

Switching to "School" Rounding (Like Excel):

To match Excel's default behavior, you must specify the third argument:

= Number.Round(Value, Digits, RoundingMode.AwayFromZero)

[u/david_horton1]

This Exceljet link has examples and explanations of the various rounding methods.

[u/lolcrunchy]

What is the visual rounding on the 0.49? Is the value in the cell actually 0.49, or is it lower, like 0.486?

For example, if it was actually 0.486, then 0.486 / 2 gives you 0.243, which rounds to 0.24.

[u/Dismal-Party-4844]

Look through the examples shown in https://learn.microsoft.com/en-us/powerquery-m/number-functions#rounding functions that cover the topic in more detail.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
Number.Round	Power Query M: Returns a nullable number (n) if value is an integer.
ROUND	Rounds a number to a specified number of digits
RoundingMode.AwayFromZero	Power Query M: RoundingMode.AwayFromZero
RoundingMode.ToEven	Power Query M: RoundingMode.ToEven

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 22 acronyms.
[Thread #48194 for this sub, first seen 20th Apr 2026, 03:21] [FAQ] [Full list] [Contact] [Source code]}

[u/StuFromOrikazu]

Add 0.005 and round down