Downloads
Power Query MList: pick and buildBeginner

List.Numbers

Builds a list of numbers: a starting number, how many numbers, and an optional step between them.

Syntax
List.Numbers(start, count, optional increment)

Returns: A list of numbers.

What it does

List.Numbers creates a sequence of numbers without typing them out. Use it for a range of document numbers, a sequence of period numbers, or a series of thresholds for a schedule. The increment is optional and defaults to 1.

Syntax and arguments

Example

Check numbers are issued in a series. The AP team wants to see every check number from 1001 through 1010, so that the unused numbers can be spotted.

let
    AllNumbers = List.Numbers(1001, 10),
    Issued = {1001, 1002, 1004, 1005, 1008, 1010},
    NotIssued = List.Difference(AllNumbers, Issued)
in
    NotIssued

AllNumbers is 1001 through 1010. NotIssued is {1003, 1006, 1007, 1009}, the gaps that a reviewer would ask about. This is the core of a sequence check.

Example: a second, different use

A step schedule: List.Numbers(0, 5, 100) gives 0, 100, 200, 300 and 400, useful for a price band or threshold table.

Things to watch

In business terms

Gap checks on sequential document numbers (checks, invoices, credit notes), period numbering and building bands for a rate table all use a number sequence. Missing numbers are exactly what an auditor wants to see.

Related functions

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