Rank column (Preview)
The Rank column command adds a new column to a table with the ranking defined by one or more other columns from the table. A Rank method option can be used to define how ties should be handled.
Note
Currently, the rank column feature is only available in Power Query Online.
The following table has fields for Team
, Total Points
, and Bonus modifier
. Imagine that you have the task of creating of a new column that effectively ranks the teams using the values from the Total Points
and Bonus modifier
fields.
Team | Total Points | Bonus modifier |
---|---|---|
A | 20 | 0.5 |
B | 30 | 0.8 |
C | 40 | 0.2 |
D | 10 | 0.45 |
E | 20 | 0.75 |
The teams shared a list of ways that they want to rank each other:
- Using only the values from the Total Points field where higher values rank higher using standard competition as the rank method
- Using only the values from the Total Points field where higher values rank higher using dense as the rank method
- Ranking first by the Total Points and then by Bonus modifier where higher values rank higher using the standard competition as rank method
For the first requirement in the shared list, use only the values from the Total Points
field, where higher values rank higher using standard competition as the rank method. To achieve this requirement, use the following steps:
With the original table already in Power Query, select the
Total Points
column. Then from the Power Query Add column tab, select Rank column.In Rank, select
Total Points
for Rank by and Rank values from high to low for the Rank criteria.By default, the rank method for this dialog is standard competition, so just select OK. This action gives you a new step with the added
Rank
column.Screenshot of the output of the rank transform, where the Total Points field was the only Rank by field and the rank criteria for it was Rank values from high to low. Team C Ranked first. Team B ranked second. Team A and Team E ranked third. Team D ranked fifth.
For the second requirement in the shared list, use only the values from the Total Points
field, where higher values rank higher using dense as the rank method. To achieve this requirement, use the following steps:
With the original table already in Power Query, select the
Total Points
column. Then from the Power Query Add column tab, select Rank column.In Rank, select
Total Points
for Rank by and Rank values from high to low for the Rank criteria.Select Advanced at the top of the dialog box. This selection enables the advanced section. In Rank method, change the value from Standard competition to Dense.
Screenshot of the advanced section of the rank dialog where the Total Points field is being used with the Higher value ranks higher rank criteria and the rank method being selected is Dense.
After selecting the rank method, select OK. This action gives you a new step with the added
Rank
column.Screenshot of the output of the rank transform, where the Total Points field was the only Rank by field, the rank criteria for it was Higher value ranks higher and the rank method was Dense. Team C ranked first. Team B ranked second. Team A and Team E ranked third. Team D ranked fourth.
For the third requirement in the shared list, rank first by the Total Points
and then by Bonus modifier
, where higher values rank higher using the standard competition as the rank method. To achieve this requirement, use the following steps:
With the original table already in Power Query, select the
Total Points
and thenBonus modifier
columns. Then from the Power Query Add column tab, select Rank column.The rank dialog appears with its advanced section open, with both fields selected in the Rank by column. Total Points is in the first row and then Bonus modifier below it. Both rows use the Rank criteria of Rank values from high to low.
Make sure that Rank method is set to Standard competition.
Screenshot of the advanced section of the rank dialog. The Total Points and the Bonus modifier fields are being used as part of the Rank by logic, in that exact order. Both have a rank criteria of Rank values from high to low. The rank method selected is set to Standard competition.
After verifying the selections, select OK. This action gives you a new step with the added
Rank
column.
A rank method establishes the strategy in which the ranking algorithm handles ties. This option is only available in the advanced section of the Rank dialog.
The following table lists all three available rank methods and provides a description for each.
Rank method | Description |
---|---|
Standard competition | Items that compare equally receive the same ranking number, and then a gap is left in the ranking numbers. For example, 1224. |
Dense | Items that compare equally receive the same ranking number, and the next items receive the immediately following ranking number. For example, 1223. |
Ordinal | All items receive distinct ordinal numbers, including items that compare equally. For example, 1234. |