Following a recent DAX pop quiz that I prepared and posted on LinkedIn, one of the people who responded to the quiz raised a fascinating idea, via which text columns no longer needed a numeric sort column to put them in proper order. I experimented with this new approach and it worked beautifully. In today’s tutorial, I’ll show you how using this approach can create self-sorting text columns that use both DAX and Power Query. You can watch the full video of this tutorial at the bottom of this blog.
Let’s take a look at the Practice Data Set that we have from our External Tools (we’ve got the Enterprise DNA Extended Date Table). In this table, we have a field named, Month Name, which is the one we’re going to focus on.
Let’s grab the Month Name and pull it out into a table. You’ll see that it’s sorted alphabetically. And so, we need to address that to get it to work properly in our tables and visuals.
How To Create Self-Sorting Columns Using DAX And Power Query In Power BI
Let’s do Power Query first. Let’s go to our Dates table and we’re going to create a column and use just a bit of simple M code. Let’s call this column, Month Name and we’re going to use an asterisk just to indicate that it’s a self-sorting column.
For the formula, we’ll use the function called Text.Repeat. It asks for the text that you want to insert or repeat, and then the number of times you want to repeat it. Instead of some explicit text, we’ll do non-printed characters. And so for that, we can use a function called Character.FromNumber and the number that we’re going to use is 0032, which is the number for a non-printing space. We want to repeat that by 12 minus the MonthOfYear. Then, we’ll concatenate it with Month Name.
That adds to our column. It looks the same, but it now has those non-printing spaces in front.
When we search for Month, we find that Month Name* field with the self-sort. We throw that into a table, and we can see that instead of sorting alphabetically, it’s actually sorting chronologically based on the spaces.
If we look here at the Sort by Column, it’s just sorted by itself. There’s no additional sort field that is being applied.
We can also do this using a very similar approach in DAX. Let me show you how to do that.
First, let’s add a custom column here. The methodology is the same. The functions are a little bit different. Let’s call this one, Month Name with a double asterisk. Then, instead of Text.Repeat, we’re going to use a function in DAX called REPT. It’s exactly the same structure. It uses the text, and then the number of times you want it to repeat.
And so, for the text, instead of Character.FromNumber, we’re going to use in DAX the equivalent, which is UNICHAR. Next, we’ll have 0032, which is our number for the non-printing space, and then have it repeat by 12 minus MonthOfYear.
We’re going to concatenate that with our Month Name field, and let’s use the original Month Name, not the Month Name*. I’m going to truncate this into a short field by using LEFT and then 3 (we’re just going to take the first three characters), just to be able to use it in a visual to show you how it’ll look.
So now, if we pull that into a table, that sorts exactly chronologically as the M code did.
This works not just in tables, but also works in visuals as well.
***** Related Links *****
DAX Function COLUMNSTATISTICS In Power BI
Extended Date Table Power Query M Function
Sorting Date Table Columns In Power BI
Conclusion
I think that this is a really cool technique to create self-sorting columns in Power BI using DAX and Power Query. I thought that it was absolute genius. I’m very thankful to the participant in the quiz for pointing it out. I hope you found this useful in terms of how you create and manage your text fields.
Cheers,
Brian
