List.Numbers
Builds a list of numbers: a starting number, how many numbers, and an optional step between them.
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
start: the first number.count: how many numbers to create.increment(optional): the step between numbers. The default is 1.
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
- The count is how many numbers you get, not the last number. For 1001 with 10 numbers, the last is 1010.
- Steps that are not whole numbers can leave small rounding differences. Round the results with Number.Round when you need exact cents.
- A sequence is only useful when the source numbers are complete and in the same type. Text numbers will not match.
- For dates, use List.Dates.
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.