Number.Round
Rounds a number to whole numbers or to a number of decimal places, halves to the even neighbour unless you pick another rule.
Group: Number. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Number.Round(number as nullable number, optional digits as nullable number, optional roundingMode as nullable number) as nullable number| Parameter | Type | Description |
|---|---|---|
number | nullable number | The number to work on; null gives null. |
digits | nullable number optional | How many decimal places to keep; 0, a whole number, when left out. |
roundingMode | nullable number optional | Where a half goes: RoundingMode.ToEven (the default), RoundingMode.AwayFromZero, RoundingMode.Up, RoundingMode.Down or RoundingMode.TowardZero. |
Returns
nullable number. The rounded number; null for null.
Remarks
- A number exactly halfway between two candidates goes to the even one: 0.5 and 2.5 to 0 and 2, 1.5 and 3.5 to 2 and 4. This is banker's rounding, and it keeps sums of many rounded amounts honest.
- For the rounding most people learn at school, halves away from zero, pass
RoundingMode.AwayFromZero. - Only a number exactly in the middle is affected by the rule: 2.4 is 2 and 2.6 is 3 whatever mode you choose, Up and Down included. To round every fraction up or down, use
Number.RoundUporNumber.RoundDown. - The number is rounded as the decimal Power Query shows for it, 15 significant digits:
Number.Round(2.675, 2)is 2.68, a half going to the even 8, though the double nearest 2.675 lies a little below it.
Examples
Halves go to the even neighbour
List.Transform({0.5, 1.5, 2.5, 3.5}, each Number.Round(_)){0, 2, 2, 4}Two decimal places
Number.Round(1234.5678, 2)1234.57Halves away from zero, as by hand
12.5 øre on an invoice line: the default gives 0.12, AwayFromZero gives 0.13.
Number.Round(0.125, 2, RoundingMode.AwayFromZero)0.13Related functions
Number.RoundUp, Number.RoundDown, Number.RoundAwayFromZero, Number.RoundTowardZero, Currency.From.