Table.Range

D

Back to top

T

Back to top

#

Back to top

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

Understanding Table.Range

The Table.Range function extracts a subset of rows from a table based on a start index and a count. The function has two arguments: the table that needs to be sliced, and the range of rows to be extracted. The range is defined by the start index, which is the position of the first row to be extracted, and the count, which is the number of rows to extract from the start index.

The syntax to use the Table.Range function is as follows:

Table.Range(Table as table, start as number, count as number)

Here, Table is the name of the table that needs to be sliced, and Start and Count are the parameters that define the range of rows to be extracted.

Examples

Let’s take a look at some examples of how Table.Range can be used.

Example 1: Extracting the first five rows of a table

Suppose we have a table named “SalesData” that contains sales data for a company. We want to extract the first five rows of the table. We can use the following formula:

Table.Range(SalesData, 0, 5)

The first argument is the name of the table “SalesData”, the second argument is the start index, which is 0, and the third argument is the count, which is 5. This formula will extract the first five rows of the SalesData table.

Example 2: Extracting rows from a specific index

Suppose we want to extract rows from the SalesData table starting from the third row. We can use the following formula:

Table.Range(SalesData, 2, 5)

Here, we are specifying the start index as 2, and the count as 5. This formula will extract the rows from the third row up to the seventh row of the SalesData table.

Example 3: Extracting the last 10 rows of a table

Suppose we want to extract the last 10 rows of the SalesData table. We can use the following formula:

let

rowCount = Table.RowCount(SalesData),

start = rowCount - 10

in

Table.Range(SalesData, start, 10)

Here, we are first calculating the total number of rows in the SalesData table using the Table.RowCount function. We are then subtracting 10 from the total row count to get the start index. Finally, we are using the Table.Range function to extract the last 10 rows of the SalesData table.

In conclusion, the Table.Range function is a powerful M function that allows you to extract a subset of rows from a table based on a start index and a count. This function is extremely useful for data analysis and visualization tasks in Power Query. By understanding how the function works and how to use it, you can perform complex data analysis tasks quickly and efficiently.

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)