Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# DAX And Power Query | Creating Self-Sorting Columns

by | 7:00 pm EST | February 25, 2022 | DAX

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

## How To Get Your Dataset’s Top N In Power BI

In today’s blog, we will walk you through the process of using Quick Measures Pro to create a custom...

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

## The CALCULATE DAX Function: Issues & Solutions

In today’s blog, we will explore why you should not use the CALCULATE DAX function to obtain Average...

## CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, you’ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

## RANKX Deep Dive: A Power BI DAX Function

Today, we will dive deep into RANKX, a scalar DAX function in Power BI that allows you to return the...

## Data Cache: What It Is And How It Helps To Optimize Queries

In this tutorial, you'll learn what a data cache is and why it's important. A data cache stores bits of...

## Get Power BI Previous Week Values Using DAX & Power Query

Retrieving previous period values in Power BI is a common task, but retrieving previous week values...

## The Ultimate DAX Guide For Beginners

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

## Row Context And Filter Context In A Power BI DAX Code

In this tutorial, we’re going to take a look at a piece of DAX code. You may have already used...

## Power BI Progress Tracker For Sales And Order Data

This tutorial will show how to create a Power BI progress tracker for sales and order data. You’ll...

## Appending Several Sheets In Excel To Power BI

This tutorial will discuss about how to import and open an Excel file with multiple sheets to one Power...

## Using DAX TOPN To Calculate Last N Weeks Trend

This tutorial will discuss and solve Problem Of The Week #5 in the Enterprise DNA forum using the DAX...