List.Generate in Power Query: Tutorial with Easy Examples

List.Generate is powerful function that allows you to create lists of values based on logic. This can be a list with text or numbers, but you can also return objects like lists, records and tables. The documentation on List.Generate is sparse, but this post gets you started.

You will see several ways on how you can generate series with List.Generate. You will learn about its syntax and see List.Generate examples.

1. Understanding List.Generate

The List.Generate function has 4 arguments, one of which optional. The syntax for List.Generate is:

= List.Generate(
   initial value,         // the starting value(s) in the list
   condition as function, // if the value satisfies the condition
   next as function,      // generate next list value
   optional selector      // selects part of the result
  )

The function starts with an initial value (the value to start the series with). Before returning a result, the function test whether the initial value satisfies a condition. If the first value satisfies the condition, it remains. The function then continues to the next value, generated by the function in argument 3.

From here, the function tests if the next value satisfies the condition in argument 2. If the value satisfies the condition, it stays. Then the function tests the next value against the condition. This loop continues until a value does not meet the condition. You can use this condition to limit the amount of values to return. Optionally you can specify an optional selector in argument 4 to determine which values to return.

With that in mind, have a look at the following examples. It creates a list of a single value:

// Returns {6}
= List.Generate(
     () => 6,      // the starting value is 6
     each _ < 7,   // as long as the value is lower than 7
     each _ + 1    // increment the initial value by 1 
)

This example:

• generates a list with 6 as its initial value. This value meets the lower than 7 condition.
• The function then creates the next value by adding 1 to the initial value. The next value is 7. Comparing this value with the condition (<7) shows the condition is not met. The next value therefore is not part of the list and the function stops here.

That’s why the result is list with a single value.

2. Generating Series with List.Generate

2.1. Consecutive Numbers

Some easy ways to generate a consecutive list of numbers with List.Generate are:

// Generates increasing series: {1, 2, 3, 4, 5, 6}
= List.Generate(
     () => 1,      // the starting value is 1
     each _ < 7,   // as long as the value is lower than 7
     each _ + 1    // increment the initial value by 1 
)

// Generates decreasing series: {6, 5, 4, 3, 2, 1}
= List.Generate(
     () => 6,      // the starting value is 6
     each _ > 0,   // as long as the value is bigger than 0
     each _ - 1    // reduce the initial value by 1 
)

Both examples give identical results. The only difference is the order in which the numbers are returned. Notice that the result of the function is contained within a list.

2.2. Number Sequence with Logic

The previous examples generated a list of consecutive numbers. Yet you may other more custom wishes for the series you create. You may want to apply logic to your values when creating a sequence with List.Generate. The examples that follow show ways to:

• increment values with steps of 2
• multiply values by 2 with every step
• multiply a value by itself with every step

// Generates the alternating series: {1, 3, 5, 7, 9}
= List.Generate(
     () => 1,      // the starting value is 1
     each _ < 10,  // as long as the value is lower than 10
     each _ + 2    // increment the initial value by 2
)

// Generates the alternating series: {1, 2, 4, 8, 16, 32}
= List.Generate(
     () => 1,      // the starting value is 1
     each _ < 50,  // as long as the value is lower than 50
     each _ * 2    // multiply the initial value by 2 
)

// Generates series: {2, 4, 16, 256, 65536, 4294967296}
= List.Generate(
     () => 2,             // the starting value is 2
     each _ < 5000000000, // as long as the value is lower
     each _ * _           // multiply the initial value by itself
)

The selector is an optional 4th argument in List.Generate. You can use it to

• select a subset of the list values.
• test a condition on the generated list values.
• perform extra transformations on the generated list values.

Here are some examples on how to use it:

// Generates increasing series: {1, 2, 3, 4, 5, 6}
= List.Generate(
     () => 1,      // the starting value is 1
     each _ < 7,   // as long as the value is lower than 7
     each _ + 1    // increment the initial value by 1 
)

// Generates increasing series: {1, 2, 3, 4, 5, 6}
= List.Generate(
     () => 1,     
     each _ < 7,   
     each _ + 1,    
     each _        // This syntax returns the list unchanged
)

// Generates increasing series: {2, 3, 4, 5, 6, 7}
= List.Generate(
     () => 1,     
     each _ < 7,   
     each _ + 1,    
     each _ + 1     // This syntax increments list values by 1
)

// Generates: {true, true, true, false, false, false}
= List.Generate(
     () => 1,     
     each _ < 7,   
     each _ + 1,    
     each _ <= 3    // Condition tests value to be <= 3
)

2.3. Fibonacci Sequence

An example Gil Raviv writes about is the Fibonacci sequence. The Fibonacci sequence is a series where each number is the sum of the two preceding ones, starting from 0 and 1. For example:

• 0 + 1 = 1
• 1 + 1 = 2
• 2 + 1 = 3
• 3 + 2 = 5

// Generates Fibonacci sequence {1, 2, 3, 5, 8, 13, 21, 34, 55, 89}
= List.Generate(
     () => [x = 0, y = 1],     // assigns a value to x and y
      each [x] + [y] < 100,    // apply function as long as < 100
      each [y = [x] + [y],     // y equals x + y of previous step
            x = [y] ],         // X changes to previous Y
      each [x] + [y]           // returns x + y
)

Notice how the selector adds up the columns x and y of each record. Without the selector, the function would have returned a list of records. You can fiddle around with the selector to return different results. You can for example:

• return a list of records
• return a single column value within a record.

// Without selector, List.Generate returns a list of records
// The first three records would look like: 
// { [x = 0, y = 1], [x = 1, y = 1], [ x = 1, y = 2] }
= List.Generate(
     () => [x = 0, y = 1],   // assigns a value to x and y
      each [x] + [y] < 4,    // apply function when (x + y) < 4
      each [y = [x] + [y],   // y equals x + y of previous step
            x = [y] ]        // X changes to previous y
)

// Returns {0, 1, 1, 2, 3, 5, 8, 13, 21, 34}
= List.Generate(
     () => [x = 0, y = 1],     // assigns a value to x and y
      each [x] + [y] < 100,    // apply function as long as < 100
      each [y = [x] + [y],     // y equals x + y of previous step
            x = [y] ],         // x changes to previous y
      each [x]                 // returns x 
)

2.4. Sequence with Factorial Numbers

You can also create a list of factorials using List.Generate. The factorial of any positive integer is the product of all positive integers less than or equal to the integer. For example:

• 0! = 0 * 0 = 1 (According to the convention for an empty product)
• 2! = 2 * 1 = 2
• 3! = 3 * 2 * 1 = 6
• 5! = 5 * 4 * 3 * 2 * 1 = 120

We can replicate this series by using List.Generate. List.Generate first generates a series that increments by 1 in each step ( named x ). And this series is used to apply the Number.Factorial function (named y).

// Returns {1, 2, 6, 24, 120, 720, 5040, 40320, 362880 }
= List.Generate(
   () => [x = 2, y = 1],  // x = increasing series, y = factorial
    each [x] <= 10,       // as long as x <= 10
    each [x = [x] + 1,    // increment x by 1 in each step
          y = Number.Factorial( [x] ) ],   // Turn into factorial
    each [y]                               // Return factorial
)

You can use this code as a general template for other series you want to make. You can for example replace the Number.Factorial function with:

• Number.Exp: to apply an exponential function to mulitply the value to the power of the number itself
• Number.Log, Number.Ln, Number.Log10: applies specific logarithms to a number
• Number.Sin, Number.Sinh: returns the (hyperbolic) sine of a number
• Number.Tan, Number.Tanh: returns the (hyperbolic) tangent of a number
• Number.Cos, Number.Cosh: returns the (hyperbolic) cosine of a number
• Number.Atan: returns the arctangent of a number
• Number.Sqrt: returns the square root of a number

For functions that require more arguments, like Number.Power, you can adept your function like:

= List.Generate(
   () => [x = 1, y = 1],  // x = increasing series, y = factorial
    each [x] <= 10,       // as long as x <= 10
    each [x = [x] + 1,    // increment x by 1 in each step
          y = Number.Power( [x], 2 ) ],   // Turn into factorial
    each [y]                               // Return factorial
)

2.5. List of Month Names

You can also create a list of names is by using List.Generate. How does that work? First you create two variables:

• Variable x: an increasing sequence up to 5
• Variable y: a date starting in january, increasing 1 month with each variable 1 increment.

After generating the variables, as a result in argument 4 you return the month name by using the Date.MonthName Function.

// Returns { "January", "February", "March", "April", "May" }
= List.Generate(
   () => [x = 1,                     //x is an increasing list
          y = #date( 2021, 1, 1) ],  // y returns date
    each [x] <= 5,                   // as long as next value <= 5
    each [x = [x] + 1,               // Increment x with 1 each step
          y = Date.AddMonths( [y], 1) ], // Add 1 month each step
   each Date.MonthName( [y] )            // Return month name of Y
)

As you can see, the selector performs a transformation on column y within the list of records. In the end it is easier to achieve the same result by creating a list of consecutive numbers and then transforming it using the List.Transform function. For educational purposes this example is useful though.

For a better understanding you can take a closer look at the code examples for List.Transform and other list functions.

2.6. List of Day Names

In a similar way you can return the days of the week.

// Returns { "Friday", "Saturday", "Sunday", "Monday".. "Thursday" }
= List.Generate(
   () => [x = 1,                       // x is an increasing list
          y = #date( 2021, 1, 1) ] ,   // y returns date
    each [x] <= 7,                     // for a 7 number sequence
    each [x = [x] + 1,                 // Increment x with 1
          y = Date.AddDays( [y], 1) ], // Add 1 day each step
   each Date.DayOfWeekName( [y] )      // Return Day Name of Y
)

This wraps things up for this article. If you can’t get enough of List.Generate make sure to continue reading on how you can use List.Generate to make API calls.