Skip to main
Power Query
  1. Blog
  2. Power Query Online

New Rank Column transform in Power Query Online

, , Monday, August 1, 2022

This content applies to: Power Query online

We’re pleased to announce that yet another new transformation has been added to Power Query Online: you can now add columns containing ranks to a table. To see how to use it, consider the following table containing sales and profit values for various products:

Sample table showcasing four rows of data and three columns

Let’s say you want to add a new column to this table containing the rank of each product, calculated according to its sales. To do this select the Sales column so that it is highlighted (as shown in the screenshot above) and then go to the Add Column tab on the ribbon in Power Query Online and click on the Rank column button:

Rank column button inside the Add column tab of the Power Query editor

When you do this the following dialog will appear:

Rank dialog in Power Query with the basic form selected to Rank by the Sales column using the Rank criteria of Higher value ranks higher

Click OK and the dialog will close, and you will see that a new column has been added to your table containing the ranks for each product by sales and that the table itself has been sorted by the values in the Sales column too:

Output of the rank transform where a new column with the name Rank has been added

In most cases this will give you the values you want, but by clicking on the Advanced radio button in the Rank dialog you’ll find more options for controlling how the rank is calculated.

First of all, notice that because Oranges and Pears have the same sales value they both have a rank of 2 and that the next product, Apples, has a rank of 4. This is because the default rank method, Standard competition, has been used. There are two other methods of calculating ranks though. Choosing the Dense method in the Advanced tab of the dialog from the Rank method dropdown:

Rank dialog using the same Rank by and rank criteria as before, but now the Advanced section of the dialog is selected and the Rank method has been selected to be Dense

…changes the rank value for Apples to 3:

Result of the operation where the rank method selected is Dense

Choosing the Ordinal rank method ignores any tied values like so:

Result when changing the rank method to be ordinal where there are no ties

A different way of handling tied values for sales is to consider profit values, and this is also possible on the Advanced tab. Clicking the “Add ranking” button allows you to add another column to sort by, so that when sales is tied then the product with the highest profit will get the highest rank:

Rank dialog in the advanced section using the Sales and Profit columns as Rank by columns with the same rank criteria of higher value ranks higher. The rank method selected is Dense.

Here’s the output:

Output table where Grapes ranks first, Pears second, Oranges third and Apples fourth

Notice now that Pears has a rank of 2 and Oranges has a rank of 3 because, even though they have the same sales, Pears has a higher value for Profit.

Learn more