Downloads
Power Query MNumberBeginner

Number.RoundDown

Rounds a number down to the next value at a set number of digits.

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

Returns: The rounded number, or null when the input is null.

What it does

Number.RoundDown always moves a value down to the next figure at the digit you choose, regardless of the half-way point. Finance uses it when a rule allows only whole units or whole periods: full cases of product that can be shipped, whole months of rent that have been earned, or whole cents where a rule says fractions are not paid.

Syntax and arguments

Example

A warehouse can only ship full cases, so the number of cases that can be invoiced is the quantity rounded down. Partial cases stay in inventory.

let
    Orders = #table(type table [Item = text, Units = number, UnitsPerCase = number],
        {{"Widget A", 130, 12}, {"Widget B", 96, 12}}),
    Cases = Table.AddColumn(Orders, "FullCases", each Number.RoundDown([Units] / [UnitsPerCase]), type number)
in
    Cases

Widget A gives 130 divided by 12, which is about 10.8, and rounds down to 10 full cases. Widget B divides exactly into 8 cases. The ten leftover units of Widget A stay in inventory.

Example: a second, different use

Round a prepaid expense schedule down to whole months, so a month is only recognized when it is complete.

Things to watch

In business terms

Prepaid amortization, full cases and complete billing periods all need whole units. Rounding down in the query matches how the business actually recognizes or ships, so the report does not show revenue or stock that cannot be used yet.

Related functions

All Power Query M functions · View as Markdown · M basics