In this blog post, I want to share how I use Power Query Editor in Power BI to get ranking-column and what were the use cases in my experience, instead of creating a dynamic ranking-DAX-measure.
So far, I only faced two situations when I thought I should create a ranking column in Power Query Editor.
The first situation was when I needed to analyze trend after the first sold date (or month) of each product. For instance, Product01 first came to the market on Jan-2021, and Product02 first came to the same market on Nov-2021. The business might need to compare the trend between Product01 and Product02 by describing the trend from the first month that each product started to sell. In this case, Jan-2021 of Product01 and Nov-2021 of Product02 should be shown as the same description, like Period01 (or 1), in order to show each data on the same starting month. And, the quantity of Feb-2021 of Product01 and Dec-2021 of Product02 should be seen on Period02 (or 2) on the visualization, and so forth.
The second case was when I tried to analyze Basketball game and comparing how each team's trend of the game results are shown per season. There are more than 20 teams who plays in the same league and each team's first game's date are not the same. In this case, it is better to analyze not by actual dates, but by an order of games of each team.
In the cases like these, a column for an axis in the visualization should look some thing like,
- The table has to be grouped by product
- In each group, the date has to be ordered by ascending
- If the date is ordered by ascending in each group properly, you can simply add an index column.
- Or, you can rank a date by ascending order.
I will show the sample that is related to finding & comparing a trend of each product's sales after the first launch.
Jan-2021 of Product01 and Nov-2021 of Product02 would be shown as 1. And, Feb-2021 of Product01 and Dec-2021 of Product02 would be described as 2.
This index column (or rank column) can be used as an axis in a visualization.
Let's start how to create this in Power Query Editor.
My sample looks like below.
Click Group By ➡ Select Product ➡ Select Operation = All Rows
The red area in the below figure,
change it like below.
The result of each Table inside the Rank column shows like below.
And then, you can simply expand the Rank column to show like below.
Or, instead of using Table.AddRankColumn( ) function, if you sort Date column to show ascending order like below, you will still see the same order after it is grouped by Product.
And then, Table.AddIndexColumn( ) function can be used like below.
After expand the column, you will still see the same result like below.
To summarize, when trying to compare the trend by installment date (or installment month) of each product or order of games of each basketball team, consider creating a new axis in Power Query Editor by using Table.AddRankColumn( ) function or Table.AddIndexColumn( ) function.
If you want to see a more detailed explanation of how to write the Table.AddRankColumn function, I highly recommend checking the link down below. It is a detailed description written by Rick de Groot (Microsoft MVP) and it also describes with an example to help easy understanding.
I hope this helps having fun using Table.AddRankColumn( ) function and Table.AddIndexColumn( ) function in Power Query Editor in Power BI.
Comments