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 has recently graduated from our apprenticeship scheme.

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. She has been involved in every aspect of the solutions built, from deployment, to data structures, to analysis, querying and UI, as well as non-functional concerns such as security and performance.

Throughout her apprenticeship, she has written many blogs, covering a huge range of topics. She has also given multiple talks focused on serverless architectures. The talks highlighted the benefits of a serverless approach, and delved into how to optimise the solutions in terms of performance and cost.

She is also passionate about diversity and inclusivity in tech. Last year, she became 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.