Power BI Rank and Custom Grouping - Enterprise DNA

Power BI Rank and Custom Grouping

No comments

As data analysts, we are often required to group and rank values from a data set in order to generate trends and insights. However, not everyone knows how to do these tasks the easy way using Power BI.

In today’s blog, we will examine a scenario that requires Power BI rank and custom grouping functions to show you how to do exactly that. You can watch the full video of this tutorial at the bottom of this blog.

For this tutorial, we will use a table of data with three columns, namely Record IDs, Priorities, and Minutes. Our goal is to identify the top 5% of the records with priorities 1 or 2 based on the maximum number of minutes.

Data Set To Create Power BI Rank and Grouping

This is how the dataset looks in Power Query. It has no attribute to separate priorities 1 and 2 from the rest, thus requiring us to use the M function Table.Partition. This will generate a helper column to segregate priorities 1 and 2 from other rows.

Data Set To Create Power BI Rank and Grouping

Power BI Function: Table.Partition

To examine how to use Table.Partition to segment our data, we will start by opening the Advanced Editor.

Power BI Function: Table.Partition

In the window that will appear, add a new line to create a new variable. Let’s call the variable Partitions. Add the function Table.Partition which requires the following information.

  • The table that we want to partition. In our case, is the Source.
  • The column to evaluate. This is the column in the return table in which the rules will be placed. That will be the Priority column.
  • The number of groups to create. This also refers to the number of tables to partition, and we want to have 2 tables. 
  • A hash function. To obtain a hash value, we need to use a number. Let’s start the hash function with each if followed by an underscore representing the current value.  Then, let’s add the condition is less than three, then return zero else one

In line 5, we will change Source to Partitions, giving us the following solution.

Power BI Function: Table.Partition

Click Done to see how it affects our table. As shown below, the Table.Partition function creates a list with two nested tables

Power BI Function: Table.Partition

Clicking Table 1 opens a preview table containing all records with a priority smaller than three (priorities 1 and 2).

Power BI Function: Table.Partition

 All remaining rows are in Table 2.

Power BI Function: Table.Partition

Since priorities 1 and 2 are now grouped in a single table, we can now use the table to calculate our top 5%. 

Power BI Function: Table.RowCount

The Table.RowCount can be used to determine the number of rows needed to meet the requirement of 5%. It will count the number of rows with priorities 1 and 2, then get the 5% of the number obtained.

To start, open the Advanced Editor window from the upper left corner.

Power BI Function: Table.RowCount

Then, we will add a new line and a new variable, which we will call nRow. We need to extract our first table by copying its variable name Partitions from line 3.

Next, we will use the positional index operator to access the first item in the list of the first table. Then, wrap the Table.RowCount operator around Partitions(0). We will then multiply it by 0.05 to get 5%. 

Also, round the number to zero decimal using the Number.Round function. Finally, we will change the last line from Partitions to nRow. 

Power BI Function: Table.RowCount

This should return two as shown below.

Power BI Function: Table.RowCount

The result means that to meet the 5% requirement, we need to mark two rows from Table 1. To do that, we can use the new M function Table.AddRankColumn. 

Power BI Function: Table.AddRankColumn

In this section, we will use the Power BI Rank feature to rank our rows. The Table.AddRankColumn is used to append a new ranking column to a table based on our comparison criteria.

To start, open the Advanced Editor window. Create Table 1 and name it t1 on a new line. Then, use the Table.AddRankColumn and add our first table, Partitions (0). We also need to assign a new column name which we will call i.

For the comparison criteria, we want to rank the minutes from high to low using {“Minutes”, Order.Descending}

Finally, add a RankKind=RankKind.Ordinal. This will force all items to be given a unique number, even if they are evaluated as equal. Then, we will change nRow to t1 to return Table 1. 

Our solution should look like this. 

Power BI Function: Table.AddRankColumn

Click Done to get a table similar to this. 

Power BI Function: Table.AddRankColumn

Adding a Boolean Filter

We will also add a boolean filter to our table because we do not want to keep the rank column anymore. Instead, what we want is to add a boolean filter to identify only the top two rows that we need to exclude. 

So, let’s go back to the Advanced Editor window and add a custom column. Add a new line with the Table.AddColumn function and call the new column Exclude.

For the columnGenerator, we can look at the newly created column using each i smaller than or equal to our nRow number, which is the number of rows to exclude. We will also assign them a data type using type logical.

Our solution should look like this.

Adding a Boolean Filter

Click Done to get a table that is similar to the one below.

Adding a Boolean Filter

Power BI Function: Table.RemoveColumns

We no longer need our helper column so we can remove it from our table. 

So, in the Advanced Editor window, let us use the Table.RemoveColumns and remove column i.

Power BI Function: Table.RemoveColumns

Then click Done

Power BI Function: Table.RemoveColumns

Combining Tables

We have created a new version of our nested Table 1 and need to create the same layout for our nested Table 2. And for that table, we need to add an exclude column where all values should be equal to TRUE. 

In the Advanced Editor and create a new variable called t2. We will add a custom column using Table.AddColumn and call Partitions (1). Then, add an Exclude column and set the columnGenerator as each true, type logical

We want to return t2 by editing line 13.

Combining Our Tables

We should get a table like this.

Combining Our Tables

The last thing we need to do is combine these two tables again. In the Advanced Editor window, add a new variable called t which equals t1 & t2

Combining Our Tables

Click Done to get our final table that looks like the one below. Now, we have a table ranked and grouped based on our given criteria, and with the top 5% marked for exclusion.

Combining Our Tables

***** Related Links *****
Ranking Analysis In Power BI: Advanced Insights From Combined Data Sets
Ranking Visualization In Power BI – Dynamic Visual
Power BI Dimensions & Groups For Filtering Tables In Reports

Conclusion

In this blog, you learned how to use the Table.Partition, Table.AddRankColumn, and Table.RemoveColumns functions in Power Query. Our sample dataset has 44 rows of priorities 1 and 2, which we successfully grouped and ranked, with the top 5% marked accordingly.

With this newfound knowledge of using these functions, you can now use Power BI rank and custom grouping features for your next data set.

All the best,

Melissa de Korte

Enterprise DNA Power BI On-Demand

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.