The world’s leading publication for data science, AI, and ML professionals.

Power BI Tips & Tricks: Custom Sort

Part of a series of Power BI Tips & Tricks

I started using Microsoft’s Power BI about two years ago. Along the way, I have picked up some tips and tricks for doing things more efficiently. This series is meant to be beginner-friendly so please let me know if something isn’t making sense to you. I would also love to hear about additional tips and tricks that you have found helpful!

You can skip down to the solutions and apply them to your own data or you can follow along with this sample data.

Photo by Avel Chuklanov on Unsplash
Photo by Avel Chuklanov on Unsplash

The Problem

Let’s say that I am working with data from a (small) college and they want to see how many students they have at each grade level. Okay great, this sounds like an opportunity to make a clustered bar chart.

I drag "Grade" to Axis and "Student ID" to Values and get something like this:

I can sort the graph by the count of Student IDs (number of students) or alphabetically by grade. But I want my data sorted in the chronological order that students progress with Freshman at the bottom and then Sophomore, Junior, and final Senior at the top.

Ugh! When I haven’t done one of these in a while, I almost always first try to add a calculated column and then sort by that. BUT that gives us an error because we have introduced a circular dependency.

But never fear! There are a couple of possible strategies to sort the data in any order that we want to.

Solution 1: Create an additional table

This is a great method when someone else is managing how the sorting should appear or if you have a lot of different levels. You can create this table directly in Power Bi:

Or you can create an additional Excel workbook or add a sheet to your existing workbook. I generally prefer to have the table be in Excel because I feel like it is easier to update when needed. Our table should look like this:

Now we are going to add this table to our Power BI file and create a relationship in our model.

Because we created a separate table, now we can create a calculated column without running into a circular dependency problem using RELATED.

The column is acting the way we would expect (Seniors are a "1", Juniors are a "2", etc.). Finally, we tell Power BI to sort our Grade column by our Sort Order column. While selecting the Grade column > Column Tools > Sort by Column > Sort Order.

And voila!

And it is now super easy to make updates. Let’s say we add a Super-Senior grade for 5th year students. All we have to do is update our Excel file, save it, and refresh Power BI.

Solution 2: Using Power Query

This is a good option if there are unlikely to be changes to how we want things sorted. We can still make updates with this option, it is just less user-friendly for those who aren’t as comfortable with Power Query.

With this option, we will start by going to the Power Query editor by selecting our "Transform Data" option.

We can do this just by pointing and clicking, so don’t panic, but we can also do this using M if you prefer. Either way works great!

If you want to point and click, select the "Conditional Column" button under the Transform ribbon in the Power Query Editor.

Then you can create an additional clause for each level:

Now just like we did for the other solution, all you have to do is exit the Power Query editor and make our new conditional column the column to sort "Grade" by.


There you have it! Two solutions for when you need to sort a column in a custom order: one using an additional table and the RELATED function and one using a conditional column in Power Query.

Which solution do you prefer? Do you have an even better solution you would like to share?

Keep knocking your head against the wall – it gets so much better!


Jenna Eagleson My background is in Industrial-Organizational Psychology and I have found my home in People Analytics. Data viz is what makes my work come to life. I mostly use Power BI but I occasionally foray into Tableau and other tools. I would love to hear more about your journey! Reach me on Linkedin or Twitter.


Related Articles