List.Generate

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.Generate

Understanding List.Generate

The List.Generate function is used to generate a list based on a set of rules. It takes four arguments:

– The initial value of the list

– A function to test whether the list should continue to be generated

– A function to generate the next element in the list

– An optional function to transform the final list

Here’s an example of how the List.Generate function works:

List.Generate(

() => 1, // initial value

each _ <= 5, // continue generating as long as the value is less than or equal to 5

each _ 2 // generate the next value by multiplying the previous value by 2

)

This code generates the following list: `[1, 2, 4, 8, 16]`.

The M Code Behind List.Generate

Now let's take a look at the M code that powers the List.Generate function.

(listGenerator as function, optional resultSelector as function) =>

let

step = (state, accumulated) =>

let

next = listGenerator(state),

newAccumulated = accumulated & {next}

in

if next = null then newAccumulated else step(next, newAccumulated),

initial = listGenerator(null),

result = if resultSelector = null then step(initial, {}) else List.Transform(step(initial, {}), resultSelector)

in

result

Let's break down what's happening here. The List.Generate function takes two arguments: a listGenerator function and an optional resultSelector function. The first function is responsible for generating the list, and the second function is responsible for transforming the final list.

The M code begins by defining a step function, which takes two arguments: state and accumulated. The state argument contains the current state of the list, and accumulated contains the current list of generated elements. The function generates the next element in the list using the listGenerator function and appends it to the accumulated list. If the next element is null (indicating the end of the list), the function returns the accumulated list. Otherwise, it calls itself recursively with the next element as the new state and the updated accumulated list.

The M code then initializes the state to null and calls the step function with the initial state and an empty accumulated list. If the resultSelector function is not null, the function transforms the final list using the List.Transform function and the resultSelector function. Otherwise, it returns the accumulated list as-is.

Use Cases for List.Generate

The List.Generate function can be used in a variety of scenarios. Here are a few examples:

- Generating a list of dates: You can use the List.Generate function to generate a list of dates based on a start date and an end date.

- Iterating through a folder of files: You can use the List.Generate function to iterate through a folder of files and perform a function on each file.

- Generating a list of random numbers: You can use the List.Generate function to generate a list of random numbers based on a set of rules.

Best Practices for Using List.Generate

When using the List.Generate function, there are a few best practices to keep in mind:

- Keep your listGenerator function as simple as possible: The more complex your listGenerator function is, the harder it will be to debug and maintain.

- Use the optional resultSelector function to transform your final list: This can help you avoid unnecessary steps in your data transformation pipeline.

- Test your function on a small set of data before scaling up: This can help you identify and fix any issues before running your function on a larger dataset.

The List.Generate function is a powerful tool for generating lists based on a set of rules. By understanding the M code behind the function, you can gain a deeper understanding of how it works and how to use it effectively in your data transformation and analysis workflows.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)