Rounding: Power Query vs. Others

Did you know that, by default, Power Query may round numbers differently than you learned in grade school? Unlike DAX, Microsoft Excel and Microsoft SQL Server, which use commercial rounding as their main or default approach to rounding, Power Query is different: it defaults to banker’s rounding.

What’s the difference between these two ways to round? What if you don’t want Power Query’s default—can you override it? What if you don’t ever intentionally round numbers, does this topic even matter to you? (Hint: Possibly yes!)

 Let’s explore some details and options.

Rounding Review

First, a little rounding review. How would you round 1.2 and 1.7 to whole numbers? Likely like this: Since 1.2 is closer to 1 than 2, round it to 1. Since 1.7 is closer to 2 than 1, round it to 2. Easy enough.

Now, what about when the part to be rounded is exactly in the middle between the two values it could be rounded to? For example, how would you round 1.5 and 2.5? Since 1.5 is halfway between 1 and 2, likely you’d round it to 2. Similarly, you’d probably round 2.5 to 3.

This style of rounding—where values that are exactly in the middle (a.k.a. tie values) are rounded away from zero—is commonly known as commercial rounding, or more technically as rounding half away from zero or half towards infinity. This is likely how you learned to round back in grade school. It is also the default (or main) rounding approach used by DAX, Microsoft Excel and Microsoft SQL Server…but not by Power Query.

Instead, Power Query defaults to banker’s rounding (a.k.a. Dutch rounding, odd-even rounding, convergent rounding, et al), which, more technically, is known as rounding half to even. Banker’s rounding differs from commercial rounding in how it handles ties: With banker’s rounding, ties are rounded to the nearest even value.

The difference can be observed by comparing how the two approaches round 1.5 and 2.5 to whole numbers: Both round 1.5 to 2—for commercial rounding, because 2 is further away from 0; for banker’s rounding, because 2 is the nearest even number. However, their results diverge in the case of 2.5—with commercial rounding, the result is 3 because it is further away from 0; but with banker’s rounding, the result is 2 because it is the nearest even number.

Neither approach is wrong; rather, which is appropriate depends on what’s desired in the given situation. Since commercial rounding is what most of us are likely used to, I’d guess that often it will be what you want.

Overriding the Default

Let’s say you want commercial rounding. Given that Power Query’s default is banker’s rounding, how do you get it to commercially round?

If you’re using Number.Round, you can set its optional third argument to control the rounding mode it uses (Power Query supports a total of five approaches). Specify RondingMode.AwayFromZero for commercial rounding:

// working with a single value
Number.Round(2.5, 0, RoundingMode.AwayFromZero) // outputs 3

// rounding an entire column
Table.TransformColumns(Source,{{"SomeCol", each Number.Round(_, 0, RoundingMode.AwayFromZero)}})

Or, skip Number.Round and second argument, and instead switch to method Number.RoundAwayFromZero. With this method, there’s no need (or option) to set the rounding mode because it always and only rounds away from zero.

Number.RoundAwayFromZero(2.5, 0) // outputs 2.5

Table.TransformColumns(Source, {{"SomeCol", Number.RoundAwayFromZero }})

Rounding When You Might Not Think

Perhaps you’re thinking, “This discussion on rounding interesting, but I never round numbers in Power Query, so its rounding modes don’t affect me.” Maybe—but are you sure Power Query never rounds on your behalf?

In the UI, do you ever use the column type dropdown menu to convert values to whole numbers or to fixed-decimal (currency) numbers? Do hand-write equivalent code (like “Table.TransformColumnTypes(Source, {{"ColA", Int64.Type}, {"ColB", Currency.Type"}})? To perform such conversions, a conversion method is applied to each value in the relevant column. Normally, this is a “.From” method whose name corresponds with the specified “.Type”. For example, if you are transforming column types to Int64.Type, the associated conversion method is normally Int64.From; similarly for Currency.Type, it’s normally Currency.From.

These conversion methods can round: Int64.From rounds off the fractional part of its input to convert it to a whole number. Currency.From rounds its input to 4 decimal places. While you didn’t explicitly call a method with “round” in its name (like Number.Round), rounding is still taking place—and since Power Query’s default is to use banker’s rounding, banker’s rounding is how these conversion methods round by default.

If you are calling one of these “.From” methods directly, you can change this default by passing an optional rounding mode argument:

Int64.From(2.5) // outputs 2
Int64.From(2.5, null, RoundingMode.AwayFromZero) // outputs 3

Currency.From(1.00005) // outputs 1
Currency.From(1.00005, null, RoundingMode.AwayFromZero) // outputs 1.0001

However, with the column type dropdown/Table.TransformColumnTypes, you’re not in control of the code invoking the .From method, so you don’t have the option to change its arguments. In this context, you can’t change the rounding mode used by the conversion method. Instead, you’ll need to make its rounding superfluous by applying the desired rounding before Table.TransformColumnTypes. This way, the “.From” method invoked by Table.TransformColumnTypes has nothing left to round (so its default rounding mode becomes irrelevant) because you’ve already reduced its inputs to be within the expected number of decimal digits.

…
Rounded = Table.TransformColumns(Source, {{"SomeCol", each Number.Round(_, 0, RoundingMode.AwayFromZero)}}),
TransformValues = Table.TransformColumnTypes(Rounded, {{"SomeCol", Int64.Type}}),
…

…
Rounded = Table.TransformColumnns(Source, {{"SomeCol", each Number.Round(_, 4, RoundingMode.AwayFromZero)}}),
TransformValues = Table.TransformColumnTypes(Rounded, {{"SomeCol", Currency.Type}}),
…

Query Folding

Let’s say you apply straight Number.Round (i.e. without specifying a rounding mode) to data coming from an external data source. It is possible you might not see the default rounding behavior as described above. Where you’d expect to see the default of banker’s rounding, you might, for example, see commercial rounding instead. Why? What’s going on?

The culprit may be query folding.

Consider the following M expression:

let
    Source = Sql.Database("localhost", "mytest"),
    Data = Source{[Schema="dbo",Item="Rounding"]}[Data],
    AddedRounded = Table.AddColumn(Data, "Rounded", each Number.Round([Number]))
in
    AddedRounded

On my system, it query folds to the following SQL:

select [_].[Number] as [Number],
    round([_].[Number], 0) as [Rounded]
from [dbo].[Rounding] as [_]

At first glance, nothing may jump out as problematic: The T-SQL is simple, with Power Query’s main rounding function (Number.Round) being transposed to T-SQL’s main rounding function (ROUND). However, there’s a catch: ROUND performs commercial rounding, unlike Number.Round’s default of banker’s rounding.

Why doesn’t query folding instead map Number.Round to a SQL Server function that does banker’s rounding, so that they match in how they round? There isn’t such a function built into T-SQL. Instead, query folding maps to the closest corresponding native data source function that exists. ROUND may not do banker’s rounding, but it does round numbers, so it is used. The net effect is that the results produced by evaluating an M expression like the above may vary, depending on how much query folding takes place.

To prevent this potential variability and ensure that banker’s rounding is always used, you could use Table.StopFolding to ensure that the rounding logic isn’t folded. For example, the above expression could be modified by adding the following step after Data but before AddedRounded:

StopFolding = Table.StopFolding(Data),

Instead, if you want commercial rounding and are using SQL Server, you don’t need to worry about Table.StopFolding: As of this writing, Number.RoundAwayFromZero isn’t query folded, nor is Number.Round when RoundingKind.AwayFromZero is specified. If, one day, query folding support is improved to fold these, then presumably (though I can’t officially promise this) it will be implemented in a way that honors the explicitly indicated rounding kind.

If you’re using a non-SQL Server data source, you may need to do some testing to determine if/how the rounding method you are using is folded.

Others vs. Power Query

Commercial rounding is the normal rounding many of us learned in school, and is the main or default rounding method for DAX, Excel, and SQL Server…unlike Power Query. If you round numbers in Power Query, it is important to be aware of this difference in behavior. It can explain why comparing the output of a legacy SQL report or Excel spreadsheet to the version you migrated to Power Query may show (perhaps slight) numeric differences…or why translating DAX code to Power Query, or vice versa, can produce numbers that don’t line up exactly.

Switching between the two rounding modes isn’t hard in Power Query, but requires a little bit of conscious effort.