A

B

C

D

E

F

G

H

I

J

L

#

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)

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)
   	Power BI Training for Facility Management Professionals (2 Days)
 	£1,990.00 Add to basketBuy Now
 
 
 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)