How to Create Single Letter Month & Day Name Columns in Power BI

Contents

1. Intro
2. Prerequisites
3. How to Create In Power Query
4. How to Create in DAX
5. How to Sort Initial Columns
6. Set the Sort Order Using Tabular Editor
7. Sort Order Workaround Using Power BI Only

Intro

One rule I always to try to stick to when visualising data, is to never use diagonal, vertical or cut-off labels on the X-axis of a time series chart.

Using a 3 letter month (Jan, Feb, Mar, Apr...) is my go-to and works well for the majority of visuals.

But, sometimes a requirement will come up that will neccessitate a small chart that hasn't got the space on the x-axis for even a 3 letter label.

Sometimes I just want to display the month initials (J, F, M, A...) or the day of the week initials (M, T, W, T, F, S, S).

This means that your end users will no longer have to crane their neck like a flamingo doing yoga.

Read on to see how to create these columns in Power Query or DAX, and how to overcome the Sort Order error that Power BI is going to throw at you.

Prerequisites

For this to work, I'm going to assume you have already got a dedicated date/calendar table with the following columns:

• Date (Date - 01/01/2020)
• Month Number (Integer - 1, 2, 3...)
• Month Name (Text - January, February, March, April...)
• Day of Week (Integer - 1, 2, 3...)
• Day Name (Text - Monday, Tuesday, Wednesday...)

If you haven't got a date table yet, check out my free Ultimate Date Table which will have these initial columns already created.

How to Create In Power Query

1. Open your Date table in Power Query.

2. Click Add Column --> Custom Column

3. Give the column a name (For example, Day Name (Initial) ) and enter the following formula. (Replace [Day Name] with the name of your column that contains the full name of the day of the week)\

   Text.Start( [Day Name] , 1 )
   This takes the first character of the string which is all we need.

4. Change the column to type Text

5. Repeat the same process to create the Month Name (Initial) column, using your
   Month Name text column instead of Day Name:\

   Text.Start( [Month Name] , 1 )

How to create in DAX

1. In Power BI Desktop, with the Date table selected, click New Column from the Modeling tab, or right click and select New Column.
2. Enter the following formula for Day Name (Initial):
   Day Name (Initial) = LEFT( 'Date'[Day Name] , 1 )
3. Create another column and use this formula to create the Month Name (Initial) column:
   Month Name (Initial) = LEFT( 'Date'[Month Name] , 1 )

How to Sort Initial Columns

Now that we have our initial columns, we can't use them just yet. If you do, you will get a result like this:

So, what's going on here?

Well, it's to do with the sort order of the columns. Power BI is sorting them alphabetically, but also grouping the months & day names with the same initials together. Saturday & Sunday are grouped as one, and so are January, June & July as well as April & August.

No worries, we'll just apply the sort order in Power BI, click on the Day Name (Initial Column) and sort by Day of Week column right? Wrong.

When we try this, we will get this "Sort by another column" error:

We can't sort the 'Day Name (Initial)' column by 'Day of Week'. There can't be more than one value in 'Day of Week' for the same value in 'Day Name (Initial)'. Please choose a different column for sorting or update the data in 'Day of Week'.

So, how do we get around this?

I'm going to suggest 2 ways. The easiest I know of is to use Tabular Editor to set the sort order. If your organisation doesn't allow the use of 3rd party tools like Tabular Editor, then see my other method of how we can get this to work using Power BI desktop alone.

Set the Sort Order Using Tabular Editor

1. In Power BI desktop, click External tools and select Tabular Editor.
2. Navigate to Tables --> Dates --> Month Name (Initial)
3. Look for the "Sort By Column" property in the properties pane.
4. Set the Sort By Column to Month
5. Do the same for the Day Name (Initial) column, and set the Sort By Column value to Day of Week.
6. Click save or press CTRL + S to save the changes and publish them back to Power BI. The sort order is now set correctly!

Sort Order Workaround Using Power BI Only

In this workaround, we are going to load the initial columns using 3 characters instead of 1, then set the sort order, then go back and change the number of characters to 1.

This forces Power BI into adopting the sort order we originally wanted.

Important - This will only work the first time you load a column to the data model. If you have already loaded an initials column, we either need to rename it so it loads as a new column, or start from scratch

1. Open your Date table in Power Query.

2. Click Add Column --> Custom Column

3. Give the column a name (For example, Day Name (Initial) ) and enter the following formula. (Replace [Day Name] with the name of your column that contains the full name of the day of the week)\

   Text.Start( [Day Name] , 3 )
   This takes the first 3 characters, making each Day Name value unique.

4. Change the column to type Text

5. Repeat the same process to create the Month Name (Initial) column, using your
   Month Name text column instead of Day Name:
   Text.Start( [Month Name] , 3 )

6. Click Close & Apply

7. Now we will be able to set the sort order without errors as each value is unique. Click on Day Name (Initial) and sort by column Day of Week.

8. Click on Month Name (Initial) and sort by column Month

9. Go back into Power Query and tweak the 2 columns we created so that they only bring in 1 character, instead of 3:
   Text.Start( [Day Name] , 1 )
Text.Start( [Month Name] , 1 )

10. Close & Apply. Now your initial columns are sorted correctly!

The same concept applies if you are using a DAX calculated column instead of Power Query. Make sure the first time you create the column, you create it using 3 characters instead of 1, set the sort order, then change it back to 1 character.