Number.Mod Function in Power Query M (5 Easy Examples)

Number.Mod returns the remainder after dividing one number by another. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want the leftover amount after a division, say to check whether a row number is odd or even, Number.Mod is the function for it. You pass it a number and a divisor, and it gives back what is left over.

Table of Contents

Syntax of Number.Mod Function

Number.Mod(number as nullable number, divisor as nullable number, optional precision as nullable number) as nullable number

where

number (required, nullable number). The dividend, the value you are dividing.
divisor (required, nullable number). The value you are dividing by.
precision (optional, nullable number). Controls how the integer division is computed. Use Precision.Double (the default) for standard floating-point math, or Precision.Decimal for exact decimal math that avoids tiny float errors.

Returns: the remainder as a number. If either number or divisor is null, it returns null.

In plain terms, you give it a value and a divisor, and it tells you what is left over after the division.

Example 1: Basic remainder

Find the remainder when you divide 17 by 5.

Number.Mod(17,5)

Result: 2

5 goes into 17 three times with 2 left over, so the result is 2.

Example 2: When the division comes out even

Find the remainder when you divide 20 by 5.

Number.Mod(20,5)

Result: 0

5 divides into 20 exactly, with nothing left over, so the remainder is 0. A 0 result is the simple way to test whether one number divides evenly into another.

Example 3: A negative dividend

Find the remainder when you divide -17 by 5.

Number.Mod(-17,5)

Result: -2

The sign of the result follows the dividend, so a negative number gives a negative remainder. This is different from Excel’s worksheet MOD function, which follows the divisor and would return 3 here.

Example 4: Flag odd rows in a table

A common use of Number.Mod is testing each row for odd or even, by taking the remainder when a row number is divided by 2.

Here is the starting Tasks table:

TaskID	RowNum
Alpha	1
Bravo	2
Charlie	3
Delta	4

Now add a column that takes the remainder of RowNum divided by 2:

let
    Source = Excel.CurrentWorkbook(){[Name="Tasks"]}[Content],
    Result = Table.AddColumn(Source,"OddFlag",each Number.Mod([RowNum],2),Int64.Type)
in
    Result

The new OddFlag column holds 1 for odd row numbers and 0 for even ones:

TaskID	RowNum	OddFlag
Alpha	1	1
Bravo	2	0
Charlie	3	1
Delta	4	0

Dividing by 2 leaves a remainder of 1 for every odd number and 0 for every even number. The same row-by-row pattern is handy when you pad codes with Text.PadStart or work out a Duration.Days count per row.

Example 5: Exact decimal results with Precision.Decimal

Find the remainder when you divide 10.5 by 0.2, using Precision.Decimal.

Number.Mod(10.5,0.2,Precision.Decimal)

Result: 0.1

With the default Precision.Double, this same calculation returns a value like 0.0999999999999994 because of how floating-point math stores decimals. Passing Precision.Decimal does the division in exact decimal math, so you get a clean 0.1. To turn that result into a formatted string, you can pass it to Number.ToText.

Things to keep in mind with Number.Mod

The result sign follows the dividend. A negative number gives a negative remainder (Example 3), so do not expect Excel’s MOD behavior here.
A divisor of 0 throws an error. You get Expression.Error: The argument to division by zero is out of range., so guard for a zero divisor before calling the function.
The default Precision.Double can show tiny float errors on decimals. Switch to Precision.Decimal when you need exact decimal results (Example 5).
A null in either argument returns null. A blank number or divisor does not error, it just produces null.

Performance and query folding

Number.Mod is simple arithmetic and very cheap to run, even across large columns. Against a foldable source like SQL Server, a modulo expression inside Table.AddColumn can fold to a server-side calculation. On Excel, CSV, or folder sources it runs locally, which is fine for the row counts those handle.

Common questions about Number.Mod

Why does Number.Mod(-17,5) not return 3 like Excel’s MOD?

Power Query and Excel’s worksheet MOD use different rules for the sign of the remainder. Power Query follows the sign of the dividend, so -17 gives -2, while Excel’s MOD follows the divisor and gives 3. If you need Excel’s behavior, add the divisor back: Number.Mod([Value],5) + (if Number.Mod([Value],5) < 0 then 5 else 0).

List of All Power Query Functions

Related Power Query Functions / Articles: