List.Range - Function | Power Query How

List.Range is a Power Query M function that returns a subset of a list. The function returns values starting at a user-defined offset. And the optional count argument sets a limit on the number of items derived from the list.

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

Syntax

List.Range(
   list as list,
   offset as number,
   optional count as nullable number,
) as list

Description

The List.Range function in the Power Query M language allows you to extract a specific subset of items from a given list. You can specify both the starting point, known as the offset, and optionally the number of items you wish to retrieve (count). The function returns a new list containing the extracted items, making it a versatile tool for data manipulation and analysis.

Examples

To better understand the practical implementation of List.Range, let’s delve into a few examples.

Starting from a Specific Offset

Let’s begin by examining a scenario where you want to extract items from a list, starting from the fourth position, which is an offset of 3:

List.Range( { "a", "b", "c", "d", "e", "f", "g", "h" }, 3 ) // Output: { "d", "e", "f", "g", "h" }

In this example, we didn’t specify the ‘count’ parameter, so the function returns all items starting from the offset position, which is ‘d’, and continues to the end of the list.

When the offset is Zero

If you set the offset to zero, the function will return the entire list, as demonstrated below:

List.Range( { "a", "b", "c", "d", "e" }, 0 ) // Output: {  "a", "b", "c", "d", "e" }

Specifying the Number of Items to Retrieve

You can also specify the number of items you want to extract from a particular offset. For instance, if you want to get the first two items from the list, you can do so like this:

= List.Range( { 1, 2, 3, 4 },    0, 2 ) // Output: { 1, 2 }

Similarly, if you want to extract two items starting from the third position, the function would be written as:

= List.Range( { 1, 2, 3, 4 },    2, 2 ) // Output: { 3, 4 }

Handling Lists with Fewer Items Than Requested

In the final example, we are dealing with a list consisting of five elements. Our objective is to return a subset of seven items, initiating from an offset position of 2. The function in this context would be expressed as:

= List.Range( { 1, 1, 2, 3, 4 }, 2, 7 ) // Output: { 2, 3, 4 }

Notice that the output only contains three items—2, 3, and 4—even though we requested seven. This highlights an important feature of the List.Range function: if the ‘count’ argument exceeds the available items in the list from the specified offset, the function will return all the remaining items. This ensures that the function doesn’t generate errors and that the output stays within the boundaries of the original list.

The List.Range function is a great tool for extracting specific ranges of items from a list. Its flexibility in allowing you to set both the starting offset and the number of items to retrieve makes it a go-to function for a wide array of scenarios. Whether you’re dealing with lists of varying lengths or different data types, this function ensures that you can extract exactly what you need while avoiding potential errors.

Learn more about List.Range in the following articles:

Create Running Totals in Power Query M (Ultimate Guide)
Learn how to easily create a running total in Power Query. Tried other approaches that are slow? These tricks will get you your cumulative values FAST! » Read more
Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.Range are:

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