This post is inspired by the article – Dense Ranking in Power Query, written by PHILIP TREACY from myOnlineTraingHub.

The first part of this post is a summary of what I read from Philip’s post. While the second part is an alternate way to solve the problem, using just a slightly different approach.

What is Dense Rank?

Again, a picture tells thousand words. What we want to achieve is to add a column of “Dense Rank” according to the scores by course. See below:

It could be achieved by creating a “helper table” with unique scores by course in descending order.

The straightforward way is to load the table into Power Query, and then sort the “Course” in ascending order; followed by “Score” in descending order.

After the sorting, we remove the column “Name” and then remove all duplicates as we want to “ignore” ties in dense rank. As there are multiple courses, we need to group the scores by course before we add an Index Column. This is achieved by using “Group By” and then adding an Index Column to the grouped tables, following by expanding the tables.

The resulted table looks like below:

Now we have created the “Helper table” for looking up the dense rank.

In Power Query, we do the lookup by “Merge Queries”. After merging the two tables, I clicked on the “empty space” of each “Table” to preview the content. You know, I am cautious (afraid of mistake 😁).

They all looked good… giving me the expected value of “Dense Rank”. So I was confident to click the “Expand” icon to get the “Dense Rank” I need…

Here’s come the weird thing

Why was that? Please refer to the post Dense Ranking in Power Query, written by PHILIP TREACY from myOnlineTraingHub for details, and the solution using Table.Buffer suggested by Philip.

In summary, steps involved are:

1. Create a “Helper Table” by duplicating the original table
2. Remove the “Name” column from the duplicated table
3. Sort “Course” in ascending order; followed by sorting “Score” in descending order
4. Remove duplicates
5. Group By “Course”; Adding “Index Column” as dense rank to the grouped tables; Expand the tables
6. Merge the “Helper Table” to the original table (Left outer join) ==> Unexpected results happened here… Need to use “Table.Buffer” before the join to avoid the problem.

An alternative way

Instead of using Table.Buffer, the result can be achieved by moving the “Sorting” steps after the “Group by” steps during the preparation of the “Helper table”.

As you see below, we didn’t do any sorting before the step “Removed Duplicated”.

Right after the step of “Remove Duplicates”, we performed the “Group By”:

The sorting is done within the grouped tables.

The formula used: = Table.Sort([Records],{{"Score", Order.Descending}})

Here’s the result:

Now we are ready to add in Index column to each table to give the dense ranks:

The formula used: =Table.AddIndexColumn([TableSorted], "Dense Rank", 1, 1, Int64.Type)

Here’s the result:

Other columns are no longer required. Right click the column header, then select “Remove Other Columns”

Next step is to “Expand” the tables:

Here we go:

Don’t forget to define the data type of each column.

Now we are ready to merge the “Helper Table” to the original table to give the final result:

After the merge, we can expand the resulted tables to give the final desired result:

Yeah… 🎉 We can sort the “Course” in ascending order; the “Score” in descending order before we load it to worksheet.

Let’s watch it in action:

Honestly, I do not fully understand the logic of “lazy evaluation” behind the scene in this situation. Nevertheless, this weird behavior reminds me again the importance of checking. No matter how confident we are with our Excel skills, we should always check the results to ensure correctness. 😉

Thanks PHILIP TREACY again for letting me use his sample file.