Power QueryExercises

Banker's rounding

Rounding halves to the nearest even digit (2.5 → 2, 3.5 → 4). It's Number.Round's default mode.

Rounding halves to even spreads the rounding error evenly, which keeps totals of many rounded values closer to the true total. Excel's ROUND function rounds halves away from zero instead, so the two can differ by one in the last digit. Pass RoundingMode.AwayFromZero to match Excel.

In the Power Query editor

Transform → Rounding → Round writes Number.Round with the default mode.

Common mistake

Comparing Power Query totals with an Excel sheet and assuming one of them is wrong when values that end in exactly 5 differ by one in the last place. Number.Round with no mode rounds 2.5 to 2 and 3.5 to 4, while Excel's ROUND gives 3 and 4. Decide which rule the report needs, pass RoundingMode.AwayFromZero when it must match Excel, and round once at the end rather than at every step, which adds rounding error to the total.

Learn more

Related functions

Practise it