List.Numbers

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will explore the M code behind the List.Numbers function and how it can be used to generate sequential numbers in Power Query.

What is the List.Numbers Function?

The List.Numbers function is a Power Query function that is used to generate a list of sequential numbers. It takes three arguments: the starting number, the count, and the optional increment value. The starting number specifies the first number in the sequence, the count specifies how many numbers should be generated, and the increment value specifies the difference between each number in the sequence.

For example, the following List.Numbers function generates a list of ten sequential numbers starting from 1:

List.Numbers(1, 10)

This will generate the following output:

{1, 2, 3, 4, 5, 6, 7, 8, 9, 10}

The M Code behind the List.Numbers Function

The List.Numbers function is implemented in M code, which is the language used by Power Query to define custom functions. The M code behind the List.Numbers function is as follows:

let

ListOfNumbers = List.Generate(

() => [Current = start, Counter = 0],

each [Counter] < count,

each [Current = [Current] + increment, Counter = [Counter] + 1],

each [Current]

)

in

ListOfNumbers

Let's break down this M code into its individual parts.

The List.Generate Function

The List.Generate function is a built-in function in Power Query that is used to generate a list based on a set of rules defined by the user. It takes four arguments: the initial state, the condition for continuing the generation, the rule for generating the next value, and the rule for extracting the current value.

In the case of the List.Numbers function, the List.Generate function is used to generate a list of sequential numbers based on the starting number, count, and increment value provided by the user.

The Initial State

The initial state is defined using a lambda expression that returns a record with two fields: Current and Counter. The Current field is initialized to the starting number provided by the user, and the Counter field is initialized to 0.

The Condition

The condition for continuing the generation is specified using a predicate that checks whether the Counter field is less than the count provided by the user.

The Next Value Rule

The rule for generating the next value is specified using a lambda expression that takes the current state as input and returns a new record with two fields: Current and Counter. The Current field is set to the current value of the Current field plus the increment value provided by the user, and the Counter field is incremented by 1.

The Extract Value Rule

The rule for extracting the current value is specified using a lambda expression that takes the current state as input and returns the value of the Current field.

Using List.Numbers in Power Query

The List.Numbers function is a useful tool for generating lists of sequential numbers that can be used in many different ways. One common use case is to generate a list of numbers that can be used as an index column for a table.

For example, suppose we have a table named SalesData that contains sales data for different products and regions. We can use the List.Numbers function to generate a list of sequential numbers that can be used as an index column for this table as follows:

1. Select the SalesData table.

2. On the Add Column tab, click the Index Column dropdown and select From 1.

3. In the dialog box that appears, specify the number of rows in the table.

4. Click OK.

This will generate a new column named Index that contains a list of sequential numbers starting from 1.

In conclusion, the List.Numbers function is a powerful tool for generating lists of sequential numbers in Power Query. It is implemented in M code using the List.Generate function and takes three arguments: the starting number, the count, and the optional increment value. The generated list can be used in many different ways, such as creating an index column for a table or generating a list of dates. By understanding the M code behind the List.Numbers function, we can have a deeper understanding of how it works and how to use it effectively in our Power Query projects.

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)