Power Query Rounding: How to Round Numbers Correctly

In Power Query, select a numeric column and use Transform > Rounding > Round to round it in the interface; for a specific rule, use the M function Number.Round. Its default midpoint rule is round-to-even, so values exactly halfway between two results may not round the way you expect from everyday school arithmetic.

What rounding means in Power Query

Rounding replaces a number with a nearby value at a chosen precision. For example, rounding 12.346 to two decimal places gives 12.35. Power Query can apply this operation while preparing data in the Power Query Editor, before the query results are loaded to an Excel worksheet or data model. It changes the numeric values in the query output; it is not merely a display format.

This distinction matters. Formatting a value as currency with two decimal places may show 12.35 while retaining a more precise underlying value for calculations. Rounding the value in Power Query instead makes the result itself 12.35. Choose based on what the downstream calculation or reporting rule requires, not only on how many digits you want to see.

Round a column using the interface

In Excel for Microsoft 365 on Windows, open the query in Power Query Editor, select the numeric column, then look on the Transform tab for the Rounding controls in the Number Column group. Choose Round, Round Up, or Round Down as appropriate and enter the number of decimal places when prompted. Ribbon layouts can vary slightly with window size and build; the commands are on the Transform tab and apply to the selected column.

In Excel, choose Data > Queries & Connections, then open the query you want to edit.
Select the column that contains the numbers. If it is not a numeric type, change its type first using the data type control on the ribbon.
On Transform, choose the required rounding command and specify the precision.
Check the preview, then choose Home > Close & Load when the transformation is correct.

The resulting operation appears as an applied step, so it will run again when the query refreshes. If the values should remain untouched for later calculations, duplicate the column first or use a custom column to keep both the original and rounded results. Power Query Editor on Mac may have a different ribbon layout or feature availability depending on the Excel release; the M functions described below provide the underlying logic wherever Power Query supports them.

Use Number.Round for a chosen rule

For a custom transformation, select Add Column > Custom Column and enter an M expression. To round the Amount field to two decimal places using Power Query’s default midpoint rule, the expression is:

Number.Round([Amount], 2)

To explicitly round midpoint ties away from zero, use:

Number.Round([Amount], 2, RoundingMode.AwayFromZero)

The first argument is the number to round, the second is the number of decimal places, and the optional third argument sets how exact midpoint ties are handled. For instance, with 2.5 and 3.5 rounded to zero decimal places, the default RoundingMode.ToEven produces 2 and 4. With RoundingMode.AwayFromZero, those values produce 3 and 4. This midpoint rule is sometimes called banker’s rounding; it is not a random result, but it can surprise people who expect every .5 to go upward.

In the Custom Column dialog, give the new column a clear name such as Rounded Amount. Power Query expressions use M syntax, not Excel worksheet formula syntax. Field names in square brackets refer to values in the current row, and the expression will be evaluated for each row.

Round Up, Round Down, and decimal places

Number.Round is for nearest-value rounding. Power Query also provides Number.RoundUp and Number.RoundDown when a process requires a directional rule. For example, with a value of 4.231 and one decimal place, RoundUp gives 4.3, while RoundDown gives 4.2. In these Power Query functions, “up” and “down” refer to rounding away from or toward zero, respectively; for negative values, that is not the same as mathematical ceiling and floor. For example, rounding -4.231 to one decimal place up gives -4.3, and down gives -4.2.

The precision argument can also be negative. A value of -1 rounds to the nearest ten, and -2 rounds to the nearest hundred. For example, Number.Round(126, -1) gives 130 under the default rule, while Number.Round(124, -1) gives 120. This is useful for grouping or reporting figures at larger scales, but check the midpoint rule if the value is exactly halfway between two tens or hundreds.

Use these functions according to the actual requirement. RoundUp is useful when a quantity must never fall short, such as calculating whole packages needed from a fractional requirement. RoundDown is useful when only complete units count. Neither is a substitute for ordinary nearest rounding when the business rule says to choose the closest value.

Choosing the midpoint rule

A midpoint occurs when the discarded portion is exactly halfway between two possible rounded results. For two decimal places, 1.235 is a midpoint between 1.23 and 1.24. With the default ToEven mode, Power Query chooses the result whose final retained digit is even. Accordingly, 1.225 rounded to two places becomes 1.22, while 1.235 becomes 1.24. With AwayFromZero, positive 1.235 becomes 1.24 and negative -1.235 becomes -1.24.

ToEven is intended to reduce systematic upward bias when many midpoint values are rounded, but it is not universally the right policy. Some invoices, contracts, tax calculations, or reporting standards specify a different method. The correct choice depends on the documented rule for the data. If a midpoint policy matters, set the mode explicitly in M rather than relying on a default that another reader may not know about.

When to round in a query

Rounding each row before summing can produce a different total from summing precise values first and rounding once at the end. Suppose three rows each contain 1.004. Rounding each row to two decimal places gives 1.00 + 1.00 + 1.00, or 3.00. Adding the original values first gives 3.012, which rounds to 3.01. Neither result is automatically wrong: one follows a per-item rounding rule, and the other follows a total-level rule.

This difference is especially important for prices, quantities, exchange rates, and allocations. Before adding a rounding step, decide whether the rule applies to individual records, subtotals, or the final total. Keep the unrounded source column when possible, particularly if someone may later need to recalculate totals at a different level. A rounded value is less precise and cannot be used to reconstruct the original digits.

Per-row rule: round each transaction or line item before aggregation when the process explicitly requires each item to be rounded.
Total rule: retain precision through the aggregation and round the final total when the requirement applies only to the reported total.
Display rule: leave the underlying data precise and format the output if the goal is only to show fewer digits.

Common problems and checks

One frequent issue is applying rounding to text rather than numbers. A column can look numeric but contain text values, symbols, or mixed entries. Set an appropriate numeric data type before rounding, and inspect any errors created during conversion. If the source contains decimal separators in a different regional format, use the correct locale when changing the type; otherwise, a value such as 1,25 may not be interpreted as intended.

Another source of confusion is floating-point precision. Power Query’s Decimal Number type uses binary floating-point representation, and some decimal fractions cannot be represented exactly in binary. A value displayed as 1.235 may therefore be stored internally as a very slightly different number. That can affect an apparent midpoint. If exact decimal behavior is important, examine the source type and the required precision, and consider whether Fixed Decimal Number is suitable. Fixed Decimal Number has a fixed precision of four decimal places, so it is not a universal solution for values needing more than four decimal places.

Also verify that the query is rounding the intended column and that the rounding step occurs at the intended point in the Applied Steps list. Changing the order of a type conversion, filter, merge, or aggregation can alter which rows or values are rounded. After making a change, compare a few known inputs—including a midpoint, a negative number, and a value with more decimal places than the target precision—to the expected output.

Power Query rounding versus Excel formatting

Excel’s number formatting changes how a cell appears; it generally does not replace the stored value. Power Query rounding changes the values produced by the query. For example, a worksheet value of 8.756 formatted to two decimal places can display as 8.76 while a calculation still uses 8.756. If a Power Query step rounds it to two places, later query steps and the loaded result use 8.76.

That difference can explain totals that do not match a report showing only two decimal places. If the report expects the sum of displayed, rounded line values, round the rows before the query’s aggregation. If it expects a sum of full-precision values displayed to two places, preserve precision until after aggregation and format the final result in Excel. Confirm which convention applies rather than using formatting as a substitute for a data rule.

Frequently Asked Questions

Does Power Query round .5 up by default?
No. Number.Round defaults to ToEven, so exact midpoint values round to the nearest result with an even final digit. Specify RoundingMode.AwayFromZero if that is the required tie rule.

How do I round a number to two decimal places?
Use Number.Round([ColumnName], 2) in a custom column, replacing ColumnName with the field name. The interface also has a Round command for a selected numeric column.

Can Power Query round to the nearest whole number or ten?
Yes. Use zero decimal places for whole numbers, such as Number.Round([Amount], 0), or a negative precision such as -1 for tens and -2 for hundreds.

What is the difference between Round Up and Round Down?
They apply directional rounding rather than choosing the nearest value. In Power Query, Round Up moves away from zero and Round Down moves toward zero. Use them only when that direction matches the rule you need.

Does changing decimal places in Power Query just change how values look?
No. A rounding transformation changes the query’s numeric result. To change only the worksheet display, load the data and apply Excel number formatting instead.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.