{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Creating Subgroup Ranks in Power Query Using Table.AddRankColumn Function\n", "> Table.AddRankColumn is the latest addition to the M language. This post shows how you can use it to create subgroup rank column \n", "\n", "- toc: true \n", "- badges: true\n", "- comments: true\n", "- categories: [powerbi, M]\n", "- hide: false" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Table.AddRankColumn" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Table.AddRankColumn is the latest addition to the M language which lets you create ranks in Power Query directly. No more buffer-sort-add index column trick which was computationally expensive and could be less accurate. With this function you can specify one or more columns to rank by and also the order (ascending/descending). This is not a replacement for the `RANKX` function in DAX. If your rank order is not going to change or need it for validation purposes, this is a great function. \n", "\n", "Read the [official documentation](https://docs.microsoft.com/en-us/powerquery-m/table-addrankcolumn) for all the function parameters. But Reza Rad has the most [comprehensive overview](https://radacad.com/adding-pre-calculating-rank-in-power-bi-using-power-query) of the function with examples so definitely check it out." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ ">note: You need to upgrade to June 2022 version of Power BI to use it in Desktop. " ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Subgroup Rank" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "In the example below, we have three products, their colors and the Units. `Rank1` column shows the rank by number of units for all the products in the ascending order. What we are looking for is the subgroup rank, i.e rank within each product type as shown in `Rank2` column. " ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " | Product | \n", "Color | \n", "Units | \n", "Rank1 | \n", "Rank2 | \n", "
---|---|---|---|---|---|
1 | \n", "Product A | \n", "Red | \n", "4549 | \n", "6 | \n", "1 | \n", "
2 | \n", "Product A | \n", "Green | \n", "5562 | \n", "10 | \n", "2 | \n", "
0 | \n", "Product A | \n", "Black | \n", "7635 | \n", "11 | \n", "3 | \n", "
3 | \n", "Product A | \n", "Yellow | \n", "15000 | \n", "12 | \n", "4 | \n", "
4 | \n", "Product B | \n", "Black | \n", "1513 | \n", "2 | \n", "1 | \n", "
5 | \n", "Product B | \n", "Red | \n", "2969 | \n", "3 | \n", "2 | \n", "
8 | \n", "Product B | \n", "Black | \n", "3860 | \n", "4 | \n", "3 | \n", "
7 | \n", "Product B | \n", "Yellow | \n", "5409 | \n", "8 | \n", "4 | \n", "
6 | \n", "Product B | \n", "Green | \n", "5491 | \n", "9 | \n", "5 | \n", "
11 | \n", "Product C | \n", "Yellow | \n", "600 | \n", "1 | \n", "1 | \n", "
10 | \n", "Product C | \n", "Green | \n", "4264 | \n", "5 | \n", "2 | \n", "
9 | \n", "Product C | \n", "Red | \n", "4839 | \n", "7 | \n", "3 | \n", "