Table.AddIndexColumn

The Table.AddIndexColumn function is part of the M language, the language used by Power Query to perform data transformations. In this article, we’ll take a closer look at the M code behind the Table.AddIndexColumn function.

What is the Table.AddIndexColumn Function?

The Table.AddIndexColumn function is used to add an index column to a table. An index column is a column that contains a unique identifier for each row in a table. This can be useful for sorting, filtering, and joining tables.

The syntax for the Table.AddIndexColumn function is as follows:

The `table` parameter is the table to which the index column will be added. The `newColumnName` parameter is the name of the new index column. The `offset` parameter is the starting value for the index column (default is 0), and the `step` parameter is the increment value for the index column (default is 1).

The M Code Behind Table.AddIndexColumn

The M code behind the Table.AddIndexColumn function is relatively simple. Here’s the code:

The first line of the code defines a new variable called `addIndex`. This variable uses the Table.AddIndexColumn function to add the index column to the table.

The second line of the code returns the `addIndex` variable, which contains the modified table.

Let’s break down each part of this code in more detail.

Defining the addIndex Variable

The first line of the code defines a new variable called `addIndex`:

The `let` keyword is used to define a new variable in M. In this case, we’re defining a variable called `addIndex`.

The `=` sign is used to assign a value to the `addIndex` variable. The value assigned to the `addIndex` variable is the result of the Table.AddIndexColumn function:

This function takes four parameters: the table to which the index column will be added (`table`), the name of the new index column (`newColumnName`), the starting value for the index column (`offset`), and the increment value for the index column (`step`).

Returning the Modified Table

The second line of the code returns the `addIndex` variable, which contains the modified table:

The `in` keyword is used to specify the expression that will be returned by the code. In this case, we’re returning the `addIndex` variable, which contains the modified table.

Examples of Table.AddIndexColumn in Action

Let’s take a look at some examples of how the Table.AddIndexColumn function can be used in Power Query.

Adding an Index Column to a Table

The most basic use of the Table.AddIndexColumn function is to add an index column to a table. Here’s an example:

This code imports a table from an Excel file, adds an index column to the table using the Table.AddIndexColumn function, and then returns the modified table.

Adding an Offset to the Index Column

You can also add an offset to the index column using the `offset` parameter. Here’s an example:

This code imports a table from an Excel file, adds an index column to the table using the Table.AddIndexColumn function, and sets the `offset` parameter to 1. This will cause the index column to start at 1 instead of 0.

Adding a Step to the Index Column

You can also add a step to the index column using the `step` parameter. Here’s an example:

This code imports a table from an Excel file, adds an index column to the table using the Table.AddIndexColumn function, and sets the `step` parameter to 2. This will cause the index column to increment by 2 instead of 1.

The Table.AddIndexColumn function is a powerful tool in Power Query that allows you to add an index column to a table. Understanding the M code behind this function can help you use it more effectively in your data transformations.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us