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.
Table of contents
If you prefer video over text, make sure to check out below video that shows exactly how to use 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.
Tip
You can use the condition argument 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 multiply 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 adapt 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 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.
Tip
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.
Thanks for reading and happy querying!
Recommended Reading…
Power Query Foundations
Beginners Guide to Power Query
List Functions (200+ Examples)
Text Functions (150+ Examples)
Creating Tables (40+ Examples)
Generate the Ultimate Date Table
Advanced Topics
Master List.Generate with Easy Examples
Deep Dive into List.Accumulate
Hi!
Congrats! Very useful!
I’m using this:
But I don’t know how to limit the range imagine from page 1 to page 400 for example.
Luis! You could try changing the second argument to something like:
Great article. I want to share that I like using the formula to generate time annual series to expand my initial summary table.
I could have a start date and an end date for projects. Visualising their, say, revenues across the timeline requires time series.
Hello Taras,
Great choice with List.Generate but for numeric values, there’s a muche simpler way with {Date.Year[Start Date] .. Date.Year[End Date]}
Greate explanation, Thank you !!!
PQ docs on MS site is really hard to understand often.
You’re very welcome, I too find the documentation very limited at this moment. I hope the examples help!