List.Generate in Power Query: Tutorial with Easy Examples

In this article, I’m going to take you on a journey to understanding the power of the List.Generate function in Power Query. In this article, we’ll explore the ins and outs of List.Generate, including its syntax and how to use it to generate sequences of numbers, apply transformations on values, and even some fun examples using date functions.

Don’t worry if you’re new to this, I’ll be sure to explain everything in a simple and easy-to-understand way. Whether you’re a beginner or a seasoned Power Query user, there’s always something new to learn with List.Generate.

So, if you’re ready to learn how to use the List.Generate function like a pro, let’s get started!

1. Understanding List.Generate

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. You can find some documentation on List.Generate is sparse, but this post gets you started.

List.Generate 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:

= 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 
)
// Returns {6}

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:

= 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(
     () => 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 
)
// Generates decreasing series: {6, 5, 4, 3, 2, 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

= 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, 3, 5, 7, 9}

= 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 the alternating series: {1, 2, 4, 8, 16, 32}

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

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:

= 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: {1, 2, 3, 4, 5, 6}

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

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

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

= 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
)
// Generates Fibonacci sequence {1, 2, 3, 5, 8, 13, 21, 34, 55, 89}

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.

= 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
)
// 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] < 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 
)
// With selector, you can return a column in a record
// Returns {0, 1, 1, 2, 3, 5, 8, 13, 21, 34}

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).

= 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
)
// Returns {1, 2, 6, 24, 120, 720, 5040, 40320, 362880 }

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? There are several ways to approach it. I will show three methods.

• The value 28 represents a date in January. Adding 28 in each step and transforming the underlying value and transforming the value to a date is the first method.
• The second method starts with a date and uses the Date.AddMonths value to increase the date by a month in each step.
• Thirdly, I show a method using variables. Variable x is an increasing sequence up to 5. Variable y is 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.

= List.Generate(
     () => 28,                // start with the value 28
     each _ < 364,            // as long as series < 364
     each  _ + 28 ,           // increase the base value by 28
     each Date.MonthName( _ ) // return the Month Name
)
// Returns a list of month names January up to December
// Method suggested by Rick Rothstein
 
= List.Generate(
       () => #date( 2021, 1, 1 ),   // start with date 1 Jan 2021
        each Date.Year(_) < 2022,   //  as long as year < 2022
        each Date.AddMonths( _, 1), // add 1 month per step
        each Date.MonthName( _ )    // return the month name
)
// Returns a list of month names January up to December
 
= List.Generate(
   () => [x = 1,                         // x is an increasing list
          y = #date( 2021, 1, 1) ],      // y returns date
    each [x] <= 12,                      // 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
)
// Returns a list of month names January up to December

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.

= 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
)
// Returns { "Friday", "Saturday", "Sunday", "Monday".. "Thursday" }

Conclusion

Well, there you have it! I’ve covered everything you need to know about the List.Generate function in Power Query. From understanding the syntax of the function, to seeing real-life examples of how to generate sequences of numbers, using transformations on values and even some fun examples using date functions.

By now, you should have a solid understanding of how to use the List.Generate function for different scenarios. This is a more advanced function so it may take a bit to wrap your head around the topic.

If you’re looking for more advanced use cases incorporating List.Generate, you can check out creating running totals, and once you’ve mastered that skill, I have an article that shows how to perform a running total by category. And if you just can’t get enough of List.Generate, make sure to keep reading about how you can use it to make API calls and do even more amazing things with Power Query. Happy generating!

Thanks for reading and happy querying!