Blog

# DAX And Power Query | Creating Self-Sorting Columns

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.

## 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

## Card Visual In Power BI: Fixing Incorrect Results

In this tutorial, you’ll learn about fixing card visual results in Power BI. Cards are native visuals...

## Showcasing And Understanding Anomalies In Power BI

Many times with the analytical work that you are completing, you may want to showcase anomalies. You...

## Using Calculate Function Inside Power BI

For this tutorial, I’ll cover how the CALCULATE function works and why it’s so crucial in Power BI....

## Enterprise DNA Learning Summit – August 2018 [Event Announcement]

The next Enterprise DNA Learning Summit is just around the corner. We're taking the content up another...

## The Ultimate DAX Guide For Beginners

The third pillar in Power BI development is DAX calculations. This tutorial contains a thorough DAX...

## Time Comparison For Non Standard Date Tables In Power BI

Running time comparison type analysis on custom calendars is a little bit more complex than if you were...

## GroupBy In Power BI: An Implied Function In VertiPaq

This tutorial will discuss about the storage engine and implied GroupBy in Power BI. You’ll learn...

## Power BI Custom Sort Using DAX

Many people believe that we cannot perform Power BI custom sort using DAX, but that is not true. DAX...

## A Deep Dive Into How The ALLSELECTED DAX Function Is Used In Power BI

The ALLSELECTED DAX function inside Power BI is one of the more advanced functions that you can utilize...

## Optimize Power BI Formulas Using Advanced DAX

I'll go over some interesting concepts that involve using several table functions to optimize your...

## Showcase Report In Power BI Using DAX Techniques

In this tutorial, I'll show you a number of techniques that you can apply in showcasing your report in...

## Currency Rates In Power BI: Handling Missing Data

In this tutorial, you'll learn how to handle missing data in a currency-rates table using DAX and power...