List.Numbers returns a list of numbers starting from a value you choose, going on for as many numbers as you want. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.
If you want to generate a sequence of numbers in Power Query, like 1 through 100, the even numbers, or a list of ID values, this is the function you reach for.
Syntax of List.Numbers Function
List.Numbers(start as number, count as number, optional increment as nullable number) as list
where
start(required, number). The first value in the list.count(required, number). How many numbers to generate. This is a count, not an end value.increment(optional, nullable number). How much to add at each step. Omit it and the step defaults to1.
Returns: a list of numbers. The list starts at start and has exactly count values, each one increment apart. A count of 0 returns an empty list.
In plain terms, you tell it where to start, how many numbers you want, and how big each jump is, and it hands you the finished list.
Example 1: Generate a simple sequence
Make a list of six numbers starting at 1.
let
Source = List.Numbers(1,6)
in
Source
Result: {1, 2, 3, 4, 5, 6}
With no increment given, the step is 1, so you get six consecutive numbers from 1.
Example 2: Count up in steps of 5
Set the third argument to control the gap between numbers.
let
Source = List.Numbers(0,6,5)
in
Source
Result: {0, 5, 10, 15, 20, 25}
Starting at 0 and stepping by 5, you get six numbers, each one 5 bigger than the last.
Example 3: Count down with a negative increment
A negative increment makes the list go down instead of up.
let
Source = List.Numbers(10,6,-2)
in
Source
Result: {10, 8, 6, 4, 2, 0}
Starting at 10 and stepping by -2, the list decreases by 2 at each step.
Example 4: Use a decimal increment
The increment can be a fraction, not just a whole number.
let
Source = List.Numbers(1,5,0.5)
in
Source
Result: {1, 1.5, 2, 2.5, 3}
Stepping by 0.5 gives you five values that climb by half each time.
Example 5: Build a list of even numbers
Start at 2 and step by 2 to get only even numbers.
let
Source = List.Numbers(2,5,2)
in
Source
Result: {2, 4, 6, 8, 10}
Any even-number sequence is just a start of 2 (or any even value) with an increment of 2.
Example 6: Create a list of ID values
A common use is building sequential IDs, like order numbers starting at 1001.
let
Source = List.Numbers(1001,6)
in
Source
Result: {1001, 1002, 1003, 1004, 1005, 1006}
Wrap this in Table.FromList or feed it into #table to turn the sequence into an index or ID column.
You can also pass the list to List.Transform to map each number into a date or a formatted label.
Things to keep in mind with List.Numbers
- The second argument is a count, not an end value.
List.Numbers(1,6)gives you six numbers, not the numbers up to6. This is the most common mistake. For a1toNlist, the count isN. If you ever need to check the length of a generated list, pass it toList.Count. - A
countof0returns an empty list. This is handy when the length is driven by another value that can be zero, so you do not have to special-case it. - Decimal increments can pick up tiny rounding error. Stepping by
0.1can land on0.30000000000000004instead of0.3because of floating-point math. Round the values if the exact decimal matters. - The result is a plain list, so wrap it to make a column.
List.Numberson its own gives you a list, not a table. Feed it toTable.FromListor#tablewhen you want an index or ID column in the query.
Common questions about List.Numbers
How do I make a list from 1 to N?
Use List.Numbers(1,N), where N is how many numbers you want. The second argument is the count, so a count of N gives you 1 through N.
How is List.Numbers different from {1..10}?
The {a..b} range operator is integer-only and includes both ends, so {1..10} is 1 through 10. List.Numbers takes a count instead of an end value and supports custom increments, including negative and decimal steps.
List of All Power Query Functions
Related Power Query Functions / Articles:
