List.Generate
Builds a list by starting from an initial value and repeatedly applying a rule until a condition fails.
List.Generate(initial, condition, next, optional selector)Returns: A list of the generated values (or of the selector's results, when you give one).
What it does
List.Generate creates a list by following a rule, the way a loop would. It starts at a first value, keeps going while a condition is true, and moves to the next value with a rule you supply. It is the tool for sequences that are not a simple step, such as month-end dates or values that grow by a percentage.
Syntax and arguments
initial: a function that returns the first value, written() => ....condition: a function of the current value that returnstruewhile the list should keep growing.next: a function of the current value that returns the following value.selector(optional): a function that turns each value into the item stored in the list. Without it, the values themselves are stored.
Example
A close calendar needs the last day of every month in 2026. Adding one month to a month end does not always land on a month end, so the rule uses Date.EndOfMonth.
let
MonthEnds = List.Generate(
() => #date(2026, 1, 31),
each _ <= #date(2026, 12, 31),
each Date.EndOfMonth(Date.AddMonths(_, 1))
)
in
MonthEnds
The result is twelve dates, from January 31 to December 31, 2026. Each step moves to the next month's last day, and the list stops once the value passes December 31.
Example: a second, different use
Numbering the months with names, using the selector: List.Generate(() => 1, each _ <= 12, each _ + 1, each Date.MonthName(#date(2026, _, 1))) returns the twelve month names in order.
Things to watch
- Make sure the condition eventually becomes false. A rule that never reaches the stop point runs forever or hits a memory limit.
- The
initialargument is a function, so write() => value, not the bare value. - For a simple run of consecutive dates, List.Dates is shorter and easier to read.
- The rule can refer only to the current value. If you need a running total, use List.Accumulate.
- Like other list work, this is calculated in the workbook, not in the source database.
In business terms
Month-end close calendars, payment schedules that move forward by a fixed number of months, and stepped budget periods are all generated this way. Pair the output with a table in the data model when you need to report on each period.