List.Range

D

Back to top

T

Back to top

#

Back to top

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

What is the List.Range Function?

The List.Range function is used to return a specified range of elements from a list. The function takes three arguments:

– list: The list from which to extract the range of elements.

– offset: The position of the first element to return from the list.

– count: The number of elements to return from the list.

The function returns a list that contains the specified range of elements.

Understanding the M Code Behind the List.Range Function

To better understand how the List.Range function works, let’s take a look at its M code:

(List as list, offset as number, count as number) as list =>

let

start = List.PositionOf(List.Skip(List.Range(List, offset), offset – 1), List.FirstN({1}, 1){0} + count – 1),

end = List.PositionOf(List.Range(List.Skip(List, offset), start – offset + 1), List.FirstN({1}, 1){0} + count),

result = List.Range(List.Skip(List, offset), start – offset + 1 + end – start)

in

result

The code may seem complex, but let's break it down.

The first line defines the function's arguments and return type. The function takes a list, an offset, and a count, and returns a list.

The second line creates a variable called "start." This variable is used to determine the starting position of the range of elements to return from the list. To do this, the List.Range function is called on the list, starting at the specified offset. The List.Skip function is used to skip the first elements of the list that are not included in the range. The List.PositionOf function is then used to find the position of the last element in the range.

The third line creates a variable called "end." This variable is used to determine the ending position of the range of elements to return from the list. To do this, the List.Range function is called on the list again, starting at the specified offset. The List.Skip function is used to skip the first elements of the list that are not included in the range. The List.PositionOf function is then used to find the position of the next element after the last element in the range.

The fourth line creates a variable called "result." This variable is used to store the range of elements to return from the list. To do this, the List.Range function is called on the list again, starting at the specified offset. The List.Skip function is used to skip the first elements of the list that are not included in the range. The start and end positions are used to determine the number of elements to include in the range.

Finally, the function returns the "result" variable.

Examples of Using the List.Range Function

Let's take a look at some examples of using the List.Range function.

Example 1: Extracting a Range of Elements from a List

Suppose we have a list of numbers:

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

We can use the List.Range function to extract a range of elements from the list. For example, to extract the elements from position 3 to position 7, we can use the following formula:

=List.Range({1, 2, 3, 4, 5, 6, 7, 8, 9, 10}, 3, 5)

This formula will return the following list:

{3, 4, 5, 6, 7}

Example 2: Extracting a Range of Elements from a Table Column

Suppose we have a table that contains a column of numbers:

| Number |

|--------|

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

| 8 |

| 9 |

| 10 |

We can use the List.Range function to extract a range of elements from the column. For example, to extract the elements from position 3 to position 7, we can use the following formula:

=Table.TransformColumns(#”Previous Step”, {“Number”, each List.Range(_, 2, 5)})

This formula assumes that the previous step in the Power Query Editor has loaded the table into Power Query. The formula transforms the "Number" column using the List.Range function and specifies the offset as 2 (to skip the first two elements in the column) and the count as 5 (to return five elements).

The transformed table will look like this:

| Number |

|--------|

| 3 |

| 4 |

| 5 |

| 6 |

| 7 |

The List.Range function is a powerful tool that can be used to extract specific ranges of elements from a list or table column. While the M code behind the function may seem complex, understanding how it works can help you to use it more effectively in your 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)