Power Query Function for Dense Ranking

Power Query Function for Dense Ranking

Recently I was having issues writing a measure in DAX to rank items properly (not an issue with the DAX, but in my understanding of the DAX). I did some searching and came across Chris Webb’s article that had a nice implementation of ranking using Power Query.

The technique he proposed uses Power Query to:

  1. sort a table by the column you want to rank by,
  2. add an index column to the table

The result of his code would give you a unique ranking number for every row, even if there are ties.

Power Query

He went on to show how you could get grouped rankings, such as by month: you can add a step at the beginning to group by those columns first (using the All Rows aggregation), then ungroup them at the end. The index value gets reset for each group of rows, and within the group, ties get a different rank value.

I wanted to adapt his technique, but where rows with ties would have the same rank. Also, I wanted rows with a null value in the column to rank by to get a null ranking.

Here’s my adaptation:

  1. Select rows with a null value in the column to rank into a table
  2. Select rows with a non-null value in the column into a separate table
  3. Add a new column (with null values) to the table with null rows
  4. Group the non-null table by the ranking column
  5. add an index column to the grouped table
  6. ungroup the table
  7. combine the null table with the ungrouped table

Step 2 excludes those rows in the table with a null value in the column you want to rank by. Step 1 and 3 keeps these null value rows to put back in the table after the non-null rows have been ranked. Step 4 allows me to have ties with the same ranking value.

I wrote it as a function. When I need to rank a table without grouping it, it’s a simple call to the RankFunction. When I want to have grouped rankings, I group the table first (using the All Rows aggregation), then use Table.TransformColumns to rank each group’s rows.

let  RankFunction = (tabletorank as table, sortcolumn as text, newcolumn as text) as table =>
    NullRows = Table.SelectRows(tabletorank, each (Table.Column(_, sortcolumn) = null)),
    NullWithIndex = Table.AddColumn(NullRows, newcolumn, each null, Int64.Type),

    NotNullRows =Table.SelectRows(tabletorank, each (Table.Column(_, sortcolumn) <> null)),
    DenseGrouping = Table.Group(NotNullRows, {sortcolumn}, {{"Groupedrows", each _, type table}}),
    SortRows = Table.Sort(DenseGrouping,{{sortcolumn, Order.Descending}}),

    AddIndex = Table.AddIndexColumn(SortRows, newcolumn, 1, 1),
    ChangedType = Table.TransformColumnTypes(AddIndex,{{"Employer Rate Rank", Int64.Type}}),

    DenseUngrouping = Table.ExpandTableColumn(ChangedType,"Groupedrows",
        List.RemoveMatchingItems(Table.ColumnNames(NotNullRows), {sortcolumn})),

    AggregateRows =  Table.Combine( { NullWithIndex, DenseUngrouping } )
in RankFunction

Here is a sample of how I use the RankFunction to rank an Employer table by their AssessmentRate, first by PayrollYear, then by PayrollYear and Industry:

  Source = GetData("Employer"),
  #"Grouped Rows" = Table.Group(Source, {"PayrollYear"}, {{"AllRows", each _, type table}}),
  AddedRank = Table.TransformColumns(#"Grouped Rows", {"AllRows", each RankFunction(_, "AssessmentRate","Employer Rate Rank")}),
  #"Expanded AllRows" = Table.ExpandTableColumn(AddedRank, "AllRows", {...column names...}, {{...column names...}),
  #"Grouped Rows1" = Table.Group(#"Expanded AllRows", {"PayrollYear", "Industry Code"}, {{"AllRows", each _, type table}}),
  #"Grouped Rows2" = Table.TransformColumns(#"Grouped Rows1", {"AllRows",
  each RankFunction(_, "AssessmentRate","Employer Rate Rank (Industry)")}),
  #"Expanded AllRows1" = Table.ExpandTableColumn(#"Grouped Rows2", "AllRows", {...column names...}, {...column names...}),
  #"Expanded AllRows1"

Here is the source table:

how to use power query in excel

I group it by Payroll Year, then call the RankingFunction using Table.TransformColumns on the AllRows column (each row has a table of rows)

Power Query and Data engineering services US and Canada

Here is the table after the TransformColumns call, with the new Employer Rate Rank column. This is the rank of the employer against all other employers in the same payroll year, based on AssessmentRate. (The rows above with 0 in the PayrollYear also have null AssessmentRate; all these rows have a null Employer Rate Rank.)

power query tutorial and Power BI Services Canada

Now I group it by PayrollYear and Industry Code and call the RankingFunction again.

Power Query Excel
Power Query Online

Here is the result, with the new Employer Rate Rank (Industry) column, which is the rank within the PayrollYear and Industry code.

Power Query and Power BI, Power BI Services Winnipeg

Check out the source post here.

Thank you for reading this post! If you enjoyed it, I encourage you to check out some of our other content on this blog. We have a range of articles on various topics that I think you’ll find interesting. Don’t forget to subscribe to our newsletter to stay updated with all of the latest information on Imaginet’s recent successful projects

discover more

Microsoft Fabric

SQL Saturday Part 2: Learning About Microsoft Fabric 

SQL Saturday Part 2: Learning About Microsoft Fabric   February 29, 2024 I’ve been digging into Microsoft Fabric recently – well overdue, since it was first released about a year ago.…

SQL Saturday

My Trip to SQL Saturday Atlanta (BI Edition): Part 1 

My Trip to SQL Saturday Atlanta (BI Edition): Part 1  February 23, 2024 Recently, I had the opportunity to attend SQL Saturday Atlanta (BI edition), a free annual event for…

Enabling Bitlocker

Enabling BitLocker Encryption with Microsoft Intune 

Enabling BitLocker Encryption with Microsoft Intune   February 15, 2024 In today’s data-driven world, safeguarding sensitive information is paramount, especially with the increase in remote work following the pandemic and the…

Let’s build something amazing together

From concept to handoff, we’d love to learn more about what you are working on.
Send us a message below or call us at 1-800-989-6022.