Table.AddRankColumn

In this article, we will explore the M code behind the Table.AddRankColumn function and how you can use it in your Power Query workflows.

What is Table.AddRankColumn?

Table.AddRankColumn is a function in Power Query that adds a rank column to your data based on a specified column. This function is useful when you want to sort and rank your data based on a particular column. The function takes two arguments: the table to add the rank column to, and the column to rank the data by.

Here is an example of how to use the Table.AddRankColumn function:

In this example, we start by creating a table from some sample data. We then transform the data by changing the data type of the “Column1” column to Int64. Next, we add an index column to the data using the Table.AddIndexColumn function. Finally, we add a rank column to the data using the Table.AddRankColumn function, ranking the data by the “Column1” column in ascending order.

How does Table.AddRankColumn work?

The Table.AddRankColumn function works by first sorting the data by the specified column. It then adds a new column to the data, which contains the rank of each row based on the sorted column. The rank is calculated by assigning a value of 1 to the first row, a value of 2 to the second row, and so on. If there are ties in the data (i.e., multiple rows with the same value in the sorted column), the function allows you to specify how to handle these ties using the optional “ties” argument.

Here is the syntax for the Table.AddRankColumn function:

The “table” argument is the table to add the rank column to, and the “column” argument is the column to rank the data by. The “order” argument is an optional argument that specifies the order in which to sort the data. You can specify either “ascending” or “descending”. If you omit this argument, the function defaults to ascending order. The “ties” argument is another optional argument that specifies how to handle ties in the data. You can specify either “first” or “last”. If you specify “first”, rows with the same value in the sorted column will be assigned the same rank, and the rank of the next row will be incremented by the number of tied rows. If you specify “last”, the rank of the next row will be incremented by one for each tied row.

Using Table.AddRankColumn in Your Workflows

Table.AddRankColumn is a powerful function that can help you rank and sort your data in Power Query. Here are some use cases for the function:

– Ranking customers by sales: You can use Table.AddRankColumn to rank your customers by the total sales they have made, allowing you to identify your top customers.

– Ranking products by popularity: You can use Table.AddRankColumn to rank your products by the number of times they have been purchased, allowing you to identify your most popular products.

– Ranking employees by performance: You can use Table.AddRankColumn to rank your employees by their performance metrics, allowing you to identify your top-performing employees.

To use Table.AddRankColumn in your workflows, simply call the function and specify the table and column to rank by. You can also specify the order and ties arguments if necessary.

Table.AddRankColumn is a powerful function in Power Query that allows you to add a rank column to your data based on a specified column. The function works by first sorting the data by the specified column and then assigning a rank to each row based on its position in the sorted data. You can use this function to rank and sort your data in a variety of ways, such as ranking customers, products, or employees. By understanding the M code behind this function, you can take full advantage of its capabilities in your Power Query workflows.

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

Upcoming Courses

Contact Us