Firstly, starting with, what is Rank function in Power BI?
So, Power BI’s Rank function returns the ranking order based on the order specified. The Rank column command adds a new column to a table, with the ranking determined by one or more other table columns. To specify how ties should be handled, use the Rank method option.
Next, a brief information on how to incorporate Rank into Power Query and Power BI:
We can add a RANK column to our table in a variety of ways, including dynamically within the Power BI report using DAX Measures or pre-calculated. If we are doing a pre-calculated rank (say, for an aggregated table or something similar), it might be better to do it in Power Query so that we can use it later. In this article, I will explain how to create RANK column in Power Query.
Now, next question which arises can be, why we are using Rank in Power Query instead of DAX measures?
When calculating the rank, the first question that must be asked to ourselves “When and where should I create it?” Is this going to be a dynamic rank calculation based on the report’s slicer selection or we want static ranking? For example, we might want to calculate a customer’s rank based on the dates selected in the slicer (which can be changed by the report user). In such a case, rank should be calculated as a DAX measure using RANKX function in Power BI. This method will generate a dynamic output based on the user’s selections in the slicers and the report.
Although calculating rank dynamically is frequently useful, it is not uncommon to require ranking on a pre-calculated basis. Assume that we want to create an aggregated table based on customers and year and we want to rank customers in that table based on their sales amount. The period and other variables will remain constant. In this case, the rank calculation can be performed as a pre-calculation. This pre-calculated aggregated table can significantly improve the performance of our report. When doing this as a pre-calculation, Power Query is a good option. Because you can do it in a Dataflow and then use the result in multiple Power BI datasets.
Now, we will go through, how to add Rank column in Power Query using Table.AddRankColumn
Sample data for reference:
The addition of rank column in Power Query has not yet been added to the graphical interface of Power Query Editor in Power BI. This means we should use the function until then. The function which we will use is Table.AddRankColumn. It is very simple function with four parameters out of which one parameter is optional. Below is the syntax of rank function:
Table.AddRankColumn(
<table>,
<new_column_name>,
<comparison_criteria>,
[<rank_method>}
)
Four Parameters of function:
The 4 parameters are explained as follows:
‘table’ refers to the table name of the table in which we want to add rank column,
‘new_column_name’ refers to the title of rank column,
‘comparison_criteria’ refers to the conditions that govern how ranking should be computed,
‘rank_method’ is an optional parameter, it refers to which ranking method should be used
Let’s look further the process of how to add pre-calculated rank column to a table:
The method described in this article makes use of Power Query, so we must be in the Power Query Editor. Power Query Editor can be accessed from the Power BI Desktop by selecting Transform Data.
After opening Power Query editor, in the ‘Applied steps’ column on right side, right click on last step and click on ‘Insert Step After’, this will result in the creation of a new step with no transformation.
We can now use the Formula bar to apply any transformation that we want.
The name of the table from the previous step is represented by #”Changed Type” in the above formula. This is the name of the table that we can pass as the first parameter. As a result, the formula could look like this:
= Table.AddRankColumn(#”Changed Type”, “Rank”,{“Sales”})
To use the Table.AddRankColumn function, we must have at least three parameters. The table name (from the previous step), the new column name “Rank” and the columns and conditions that will be used to calculate the ranking. In this example, we simply said “Sales” column for ranking, with no order specified, implying that values are sorted ascending by default. The result will be as follows:
Ranking condition:
We can change the order by adding the condition still in the third parameter of the function using Order.Descending.
= Table.AddRankColumn(#”Changed Type”,”Rank”,{“Sales”,Order.Descending})
Suppose we want to do the ranking based on two columns, then the expression will be as below:
= Table.AddRankColumn(#”Changed Type”,”Rank”,{{“Sales”,Order.Descending},{“Customer”}})
Ranking Methods:
A ranking method establishes the strategy in which ties are handled by the ranking algorithm. There are 3 ranking methods:
- Standard competition: This is the rank type that is used by default. This means that if there is a tie, the items in the tie will have the same rank as the items after them, and the items after that will have the rank starting after the collection of tie numbers in that table. In short, items that compare equally receive the same ranking number, leaving a gap in the rankings. For instance, ranking sequence will be 1224. Expression for this is as below:
= Table.AddRankColumn(#”Changed Type”,”Rank”,{“Sales”,Order.Descending},[RankKind=RankKind.Competition])
- Dense: Items that compare equally receive the same ranking number, and subsequent items receive the ranking number immediately following. As an example, 1223. Expression for this is as below:
= Table.AddRankColumn(#”Changed Type”,”Rank”,{“Sales”,Order.Descending},[RankKind=RankKind.Dense])
- Ordinal: This is the ranking that would calculate the ranking based on the condition you have determined, but if there is a tie, it will give the higher rank to the record that is positional first in the table’s original order of the rows. For instance, 1234. Expression for this is as below:
= Table.AddRankColumn(#”Changed Type”,”Rank”,{“Sales”,Order.Descending},[RankKind=RankKind.Ordinal])
Conclusion:
There are two ways to rank data in Power BI: dynamic and static (or pre-calculated). If you want a dynamic ranking, DAX measures can be the best option for that. You can make use of functions like RANKX for dynamic ranking. However, if you are ranking in a pre-aggregated table, ranking with Power Query can be useful, as explained in this article.
Very Informative & Nice Blog!!