Number.RoundDown Function in Power Query M (5 Examples)

If you want to always round a number down in Power Query, never up, the Number.RoundDown function does exactly that. It floors a value to a whole number or to a set number of decimal places. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of Number.RoundDown Function

Number.RoundDown(number as nullable number, optional digits as nullable number) as nullable number

where

number (required, nullable number). The value you want to round down. If number is null, the function returns null.
digits (optional, nullable number). How many decimal places to round down to. Omit it and it defaults to 0, rounding down to a whole number. It can be negative to floor to tens, hundreds, and so on.

Returns: the number rounded down toward negative infinity at the requested precision. If number is null, it returns null.

In plain terms, you give it a number and how precise you want it, and it hands back the largest value at that precision that is still less than or equal to your input.

Example 1: Round down to a whole number

Round a single value down to the nearest integer by leaving out the digits argument.

Number.RoundDown(8.96)

Result: 8

With no digits given, it floors to a whole number, so 8.96 drops to 8 even though it is close to 9. RoundDown never rounds up.

Example 2: Round down to two decimal places

Set digits to 2 to keep two numbers after the decimal point and drop the rest.

Number.RoundDown(19.4827,2)

Result: 19.48

The digits past the second decimal are simply chopped off. There is no rounding up regardless of what those digits are.

Example 3: A negative number floors toward negative infinity

This is the part that catches people out. Round down a negative value.

Number.RoundDown(-4.2)

Result: -5

You might expect -4, but you get -5. RoundDown moves toward negative infinity, so for a negative number it goes further from zero, not closer to it.

Example 4: Floor to the nearest hundred with negative digits

A negative digits value rounds down to the left of the decimal point.

Number.RoundDown(15847,-2)

Result: 15800

Passing -2 floors to the nearest hundred. Use -1 for the nearest ten, -3 for the nearest thousand, and so on.

Example 5: Round down a calculated column over a table

Most of the time you are flooring a whole column at once, not a single value.

Say you have a FuelLog query of litres pumped per vehicle and the billing system only charges on whole tenths of a litre.

Here is the starting data:

Vehicle	Litres
Van-01	42.87
Van-02	38.29
Van-03	55.06
Van-04	47.93

Add a rounded-down column with Table.AddColumn:

let
    Source = Excel.CurrentWorkbook(){[Name="FuelLog"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Litres",type number}}),
    Added = Table.AddColumn(Typed,"BilledLitres",each Number.RoundDown([Litres],1),type number)
in
    Added

Number.RoundDown([Litres],1) runs once per row and floors each reading to one decimal.

The result adds the billed column:

Vehicle	Litres	BilledLitres
Van-01	42.87	42.8
Van-02	38.29	38.2
Van-03	55.06	55
Van-04	47.93	47.9

Note the Table.TransformColumnTypes step. It sets Litres to a real number first, so Number.RoundDown has a number to work with.

Things to keep in mind with Number.RoundDown

RoundDown floors toward negative infinity, it does not drop the fraction. For a negative number this means it goes further from zero, so Number.RoundDown(-4.2) returns -5, not -4.
RoundDown equals Truncate only for positive numbers. Both give 8 for 8.96, but on negatives they split: Number.RoundDown(-4.2) is -5 while Number.Truncate(-4.2) is -4. Reach for Number.Truncate when you want the toward-zero behavior.
RoundDown never rounds up, unlike Number.Round. Number.Round goes to the nearest value and can move up, while RoundDown always moves down. Use Number.Round for nearest and Number.RoundUp when you need the ceiling.
digits accepts negative values. Pass -2 to floor to the nearest hundred, -3 for the nearest thousand, and so on, instead of rounding decimals.
Imported text columns need a number type first. A column read as text is not converted for you and the step errors. Set it with Table.TransformColumnTypes(..., type number) first, as in Example 5.

Common questions about Number.RoundDown

What is the difference between Number.RoundDown and Number.Truncate?

Number.RoundDown floors toward negative infinity, while Number.Truncate chops the fractional part toward zero. They agree on positive numbers but diverge on negatives: Number.RoundDown(-4.2) is -5 and Number.Truncate(-4.2) is -4.

What is the difference between Number.RoundDown and Number.Round?

Number.Round goes to the nearest value and can round up, breaking ties with a rounding mode. Number.RoundDown always rounds down regardless of how close the value is to the next number, so it never goes up.

List of All Power Query Functions

Related Power Query Functions / Articles: