Power QueryExercises

Rounding numbers

Round results to a fixed number of decimals and know which rounding mode applies.

~8 min read · beginner · Module 7: Numbers, dates and conditional columns

By Tahir Nas · Examples run in our simulator · Published · Updated

No prerequisites: you can start here.

Calculations like price × 1.2 produce values with long tails, and binary floating point adds noise: 0.1 + 0.2 is 0.30000000000000004. Rounding makes results presentable and comparable.

Which way do halves go?

By default Number.Round uses round half to even (banker's rounding): a value exactly halfway goes to the nearest even digit. Many accounting rules expect away from zero instead. Compare the modes on the same values:

let
    Values = {0.5, 1.5, 2.5, -2.5, 2.4},
    Rows = List.Transform(Values, each {_, Number.Round(_, 0), Number.Round(_, 0, RoundingMode.AwayFromZero), Number.RoundUp(_), Number.RoundDown(_)})
in
    #table({"Value", "Round (default)", "AwayFromZero", "RoundUp", "RoundDown"}, Rows)

Result

ValueRound (default)AwayFromZeroRoundUpRoundDown
0.50110
1.52221
2.52332
-2.5-2-3-2-3
2.42232

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

RoundUp and RoundDown go towards positive and negative infinity, which matters for negative values.

Say which rule you need explicitly. A query that relies on the default will disagree with a spreadsheet that uses Excel's ROUND (away from zero) on exactly the halfway cases, which in money are common: 2.125 rounded to two places, for instance.

Adding tax and rounding

let
    Source = #table({"Item", "Net"}, {{"Kite", 11.49}, {"Reel", 3.99}, {"Line", 0.1}}),
    Gross = Table.AddColumn(Source, "Gross", each Number.Round([Net] * 1.2, 2, RoundingMode.AwayFromZero), type number)
in
    Gross

Result

ItemNetGross
Kite11.4913.79
Reel3.994.79
Line0.10.12

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

When to round

Round at the end of a calculation, not in the middle. Rounding each line and then summing can differ from summing and then rounding by a few cents, and both may be 'right' under different policies. Agree the rule with whoever owns the numbers, then apply it at one point in the query.

Rounding also helps comparisons. Two calculations that should agree can differ in the fifteenth decimal place because of floating point, so a = b is false. Compare rounded values, or test that the difference is below a small tolerance: Number.Abs(a - b) < 0.005.

Where to find this in Excel

Excel for Windows
Add Column > Rounding > Round (Round Up and Round Down are there too)
What you see
The rounded values go in a new column. The same commands on the Transform tab change the column in place.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Add a column based on a data type (Power Query) (Microsoft Support).

Common mistakes

  • Assuming 2.5 always rounds to 3; the default rounds it to 2.
  • Rounding every line before totalling when the policy says to round the total.
  • Using rounding to fix display, then summing the rounded values.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: Transform → Rounding → Round… writes Number.Round with the default (half to even) mode; add RoundingMode.AwayFromZero by editing the step.
  • Excel's worksheet ROUND function rounds halves away from zero, unlike Power Query's default.
  • The simulator supports all five RoundingMode values and Number.RoundUp, RoundDown, RoundTowardZero and RoundAwayFromZero.
Functions in this lesson:Number.RoundNumber.RoundUpNumber.RoundDown

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.

Practise: Add 20% VAT and round to cents (kite shop)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.