List.Generate

Updated on
Rick de Groot

List.Generate is a Power Query M function that creates a list of values based on provided functions for generating, testing, and iterating through candidate values. The function returns a list of values generated using the initial, condition, and next functions, with an optional selector function to transform the items.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.Generate(
   initial as function,
   condition as function,
   next as function,
   optional selector as nullable function,
) as list
ArgumentAttributeDescription
InitialThis is the starting value of the list.
ConditionThe latest list value gets tested against a condition. Only if this condition is true the function returns a next value.
NextContains the function that creates the next value.
SelectorOptionalUsed to transform the items in final list, i.e. you can use it to return a field from a record or to apply a transformation on the values.

Description

Generates a list of values using the provided functions. The initial function generates a starting candidate value, which is then tested against condition. If the candidate value is approved, then it’s returned as part of the resulting list, and the next candidate value is generated by passing the newly approved value to next. Once a candidate value fails to match condition, the list generation process stops. An optional parameter, selector, may also be provided to transform the items in the resulting list.

Examples

Create a list by starting at 6, repeatedly incrementing by 1, and ensuring each item is smaller than 7.

= 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}

Generate a list of increasing numbers starting at 1, taking steps of 1 and ending at 6. The next value would be 7 and does not satisfy the condition. List.Generate therefore stops. 

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

Generates a list of decreasing values from 6 to 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 
)
// Generates decreasing sequence : {6, 5, 4, 3, 2, 1}

You can use the 4th argument to apply an operation on the generated list. 

= 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}

You can include other functions within List.Generate to create your desired output: 

= 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

In case of more complex logic, List.Generate allows you to define variables. To do that simply include a record both as initial value and as your next function. 

= 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" }

Learn more about List.Generate in the following articles:

Other functions related to List.Generate are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-generate

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy