List.Generate Function in Power Query M (5 Easy Examples)

List.Generate builds a list by starting from an initial value and repeatedly applying your own rules until a condition stops it. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want to create a list from scratch, like a running sequence, a doubling series, or a custom loop, and there is no ready-made function for the pattern you need, this is the function that lets you build it yourself.

Table of Contents

Syntax of List.Generate Function

List.Generate(initial as function, condition as function, next as function, optional selector as nullable function) as list

where

initial (required, function). A zero-argument function that returns the starting value, written as ()=>startvalue.
condition (required, function). A function that takes the current value and returns true to keep going or false to stop.
next (required, function). A function that takes the current value and returns the next one.
selector (optional, nullable function). A function that picks what actually goes into the list from each value. Omit it to keep the whole value.

Returns: a list built by running the loop. Each pass checks the condition, and as long as it is true the current value (or the part picked by selector) is added before next produces the following value.

In plain terms, you tell it where to start, when to stop, and how to step from one value to the next, and it hands back the whole sequence as a list.

Example 1: Build a simple counting sequence

Start at 1 and keep adding 1 while the value stays at 8 or below.

let
    Source = List.Generate(()=>1,each _ <=8,each _ +1)
in
    Source

Result: {1, 2, 3, 4, 5, 6, 7, 8}

The each _ refers to the current value. It starts at 1, and each _ +1 steps it up by one each pass until the condition _ <=8 turns false.

Example 2: Double a value until it crosses a limit

Start at 1 and keep doubling while the value is under 100.

let
    Source = List.Generate(()=>1,each _ <=100,each _ *2)
in
    Source

Result: {1, 2, 4, 8, 16, 32, 64}

The next value after 64 would be 128, which fails the _ <=100 check, so the loop stops and 128 is never added.

Example 3: Count down in fixed steps

Start at 1000 and subtract 200 each pass while the value is still above 0.

let
    Source = List.Generate(()=>1000,each _ >0,each _ -200)
in
    Source

Result: {1000, 800, 600, 400, 200}

The step is each _ -200 and the condition is each _ >0. After 200 the next value is 0, which is not greater than 0, so the loop ends.

Example 4: Carry extra state with a record

So far the current value has been a single number. You can make it a record instead, which lets you track more than one thing while the loop runs.

Here the value is a record with a Week counter and a Total. The selector (the fourth argument) then pulls out just the Total for the final list.

let
    Source = List.Generate(()=>[Week=1,Total=50],each [Week]<=5,each [Week=[Week]+1,Total=[Total]+50],each [Total])
in
    Source

Result: {50, 100, 150, 200, 250}

The condition [Week]<=5 reads the Week field to decide when to stop, the next step bumps both fields, and each [Total] is the selector that keeps only the Total value from each pass.

Example 5: Generate a Fibonacci sequence

This is the same record idea taken a step further. The value holds two numbers, a and b, and each step shifts them along so every value is the sum of the previous two.

let
    Source = List.Generate(()=>[a=1,b=1],each [a]<50,each [a=[b],b=[a]+[b]],each [a])
in
    Source

Result: {1, 1, 2, 3, 5, 8, 13, 21, 34}

The next step sets a to the old b and b to the sum of the old a and b, while each [a] is the selector that collects each a into the list. The loop stops once a reaches 50 or more.

Things to keep in mind with List.Generate

There is no built-in row cap. A condition that never turns false loops forever and hangs the refresh. Make sure your next step actually moves the value toward the stop condition.
The starting value must pass the condition too. initial produces a candidate that is tested before it is added, so if the very first value fails the condition you get an empty list {}, not a one-item list.
Use it only when no simpler function fits. For a plain number range, {1..8} or List.Numbers is shorter. Reach for List.Generate when the next value depends on the previous one in a custom way.

Common questions about List.Generate

What is the difference between List.Generate and List.Accumulate?

List.Generate builds a new list of its own length, deciding when to stop with a condition. List.Accumulate walks an existing list once and folds it down to a single result.

Can the generated values be something other than numbers?

Yes. The value can be any type, including text, dates, or records. Examples 4 and 5 use records so the loop can track several values at once.

List of All Power Query Functions

Other Power Query Related Articles:

Related Power Query Functions / Articles: