Skip to content
Carmel Eve By Carmel Eve Software Engineer I
Quick tip – Updating the sort order of a column in Power BI (avoiding circular references)

TL;DR: Create a look up table which defines the order. Then, create a calculated column in the main table which retrieves the order from this look up, and sort the target column using the calculated column.

Problem

So, say we had a table which contains people's birth month and year:

People table containing birth month and year.

If we sort by name:

People table sorted by name.

Or by year:

People table sorted by year.

Then we get the expected results. However, if we sort by month:

People table sorted by month. Months out of order.

We clearly would expect different behaviour. To alter the sort order, we need to define an order column in the table and tell the month column to instead sort based on that order. We need to create the following column:

People table with MonthNumber column.

And then sort the month column by its order:

People table sorted by month number.

So, we could try doing this with a calculated column:

Calculated column for month order.

However, when we try to sort on the created column, we get this error:

Circular reference error produced when sorting months by month order calculated column.

If we instead create a new column using the query editor, we can avoid this circular reference. However, the problem with this approach is that Power Query doesn't have a built-in SWITCH function, so you end up with a large number of IF statements. This can get quite unwieldy if you have many different values to compute.

Solution

So, instead, we can define the column using relationships. To achieve this, we create a look-up table:

Month look up table.

This table needs to be created in the Power Query editor, not calculated from the original table, otherwise you end up with another circular reference. We then create a relationship between the look-up and original tables (sometimes this will have been detected for you):

Relationships between People and Month tables.

And then create a calculated column using the lookup:

Calculated MonthNumber column created using lookup table.

We can then sort the Month column by our calculated column, and the behaviour will work as expected:

People table sorted by birth month. Months in order.

Carmel Eve

Software Engineer I

Carmel Eve

Carmel is a software engineer, LinkedIn Learning instructor and STEM ambassador.

Over the past four years she has been focused on delivering cloud-first solutions to a variety of problems. These have ranged from highly-performant serverless architectures, to web applications, to reporting and insight pipelines and data analytics engines.

In her time at endjin, she has written many blog posts covering a huge range of topics, including deconstructing Rx operators and mental well-being and managing remote working.

Carmel's first LinkedIn Learning course on how to prepare for the Az-204 exam - developing solutions for Microsoft Azure - was released in April 2021. Over the last couple of years she has also spoken at NDC, APISpecs and SQLBits. These talks covered a range of topics, from reactive big-data processing to secure Azure architectures.

She is also passionate about diversity and inclusivity in tech. She is a STEM ambassador in her local community and is taking part in a local mentorship scheme. Through this work she hopes to be a part of positive change in the industry.

Carmel won "Apprentice Engineer of the Year" at the Computing Rising Star Awards 2019.

Carmel worked at endjin from 2016 to 2021.