Generate Year Month Day Combinations in Power BI Report using Power Query Date.ToText Function

A common requirement when reporting data with a Date axis is to be able to see the year and the month in a short form beside each other. Formats such as 2021 Jan, or Jan 2021, or even Jan 21, or 2021-01 are very popular. In Power BI, ideally, you should have a Date dimension with columns for this. However, if you don’t I’ll explain how you can create these formats easily using Power Query. I have already explained a DAX method to do it, you can check that out here.

Video

Sample table

My sample table just have a single column; Date as below;

Power Query Editor

As this transformation is going to be using the Power Query Editor, you need to click on Transform Data to open the Power Query Editor for it;

Label column

If you are going to use the month NAME as part of the year-month combination, then you will need a label column and a code column. The label column is used to show the year-month combination in the visual, and the code column to sort the label column. let’s start with the label column;

To create a column for the year and month combined, one of the most helpful functions will be the Date.ToText function. You can use the Format function like below;

Date.ToText([Date],"yyyy MMM")

This can be a custom column added to your table using the Add Columns tab > Custom Column;

Please note that Power Query is case-sensitive, and the characters of y and M should be used exactly as you see in the formula and screenshot above. The [Date] in the above expression is the name of the Date column in the table.

Date.ToText Power Query Function

The Date.ToText function can work with just one input parameter which is the date, and it will convert it to the string format of that date (depends on the locale setting on the machine). There are two optional parameters, which one of them is really helpful; format. the format can use a combination of characters to represent different parts of the date.

The third parameter of the Date.ToText is culture, which you can set it up to get locale-based conversion of date to text.

Other format string options

Here are some other format options available if you are interested;

Note that some of the format string options above may bring a different result depending on the locale. To get the right result for your locale, I suggest setting the third parameter of the function in addition to the format string

Code column

This column above by itself usually isn’t enough, as the order of items might not be the right order if you used a format that produces the month name instead of the number. You would need a column that is The Year-Month code and you can use that to sort the Year-Month column.

This code generates a six-digit text for year and month combined which will be always in the right order if converted to a number;

Date.ToText([Date],"yyyyMM")

After creating the name and code columns, click on Close and Apply to load the data into Power BI

Sort by Column

It doesn’t matter what format you used for the label column, the code column can sort it, by using the Sort by Column option. I explained more about this feature here in this article.

And this gives me the right ordered list of year-month values as below in any visual;

Hide the code column

Note that, it is recommended to hide the Year-Month code column from the report view. To learn more about what columns are a good candidate to be hidden read my article here.

Date Dimension

I strongly suggest using a Date dimension (or calendar table) including all the columns for slicing and dicing by date-related attributes. A full script of a date dimension is available here in this article for you available to download;

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.