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.

FormatSample
Date.ToText([Date],”yyyy MMM”)2020 Jan
Date.ToText([Date],”yyyy MM”)2020 01
Date.ToText([Date],”yyyy MMMM”)2020 January
Date.ToText([Date],”yyyy M”)2020 1
Date.ToText([Date],”MMM-yy”)Jan-20
Date.ToText([Date],”MMMyy”)Jan20

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;

Format stringDescriptionSample
dshort date1/01/2020
Dlong dateWednesday, 1 January 2020
ddday two-characters1
dddday name three-charactersWed
ddddday name fullWednesday
m or Mday and month combination1 January
MMmonth number two-characters01
MMMMonth name three-charactersJan
MMMMMonth name fullJanuary
y or YMonth and year combinationJanuary 2020
yytwo characters year number20
yyy or yyyyyear number four characters2020
short date and time1/01/2020 12:00:00 am
flong date and short timeWednesday, 1 January 2020 12:00 am
Flong date and long timeWednesday, 1 January 2020 12:00:00 am
gshort date and short time1/01/2020 12:00 am
Gshort date and long time1/01/2020 12:00:00 am
o or Odatetime with miliseconds2020-01-01T00:00:00.0000000
r or RDate and time with timezoneWed, 01 Jan 2020 00:00:00 GMT
sshort date time2020-01-01T00:00:00
ttime12:00 am
Ttime with seconds12:00:00 am
ushort datetime and timezone2020-01-01 00:00:00Z
ttam or pmam
zztimezone+13
ssseconds40
mmminutes50
hhhours (12-hours basis)2
HHhours (24-hours basis)14
zzztimezone full+13:00

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

4 thoughts on “Generate Year Month Day Combinations in Power BI Report using Power Query Date.ToText Function

  • About adding a calendar table: I have a report with tens of date-fields in different tables. I cannot connect all of these (joined) tables to one calendar-table. Should I create tens of calendar-tables?

    • Hi Michiel
      I would at first ask WHY you cannot connect all other tables to one calendar-table? that should be possible normally if the model is designed in the right way and with no bi-directional relationships.
      However, if you are facing a role-playing dimension scenario; meaning you have more than one date field in a table, you can use this method.
      Cheers
      Reza

  • Power BI has a drop down in Column Tools where you can select a predefined date format (eg. Short Date, Long Date, dd-mmm-yy, etc.). I’ve only recently discovered you can type in your own custom formats there as well. There is no “Custom” selection like Excel. You just type the code in the drop down. You wouldn’t know it worked unless someone shows you!

    Of course, the formatting codes are slightly different than what works in Power Query or the DAX FORMAT function or the Excel custom formatting codes, but you can do cool things like “Month”-m to get Month-1 or “#ClassOf”yyyy to get #ClassOf2021. The best part is the date column is still a date, but looks like text. In my smaller models, this often removes the need to create a Date Dimensions just to format a date.

    For numbers, you can use custom format strings to force units to display in thousands or millions everywhere in the report without needing a separate DAX formula to return the number as text. Use a custom number format of #,0K, for thousands and #,0M,, for millions. Display Units needs to be set to None if you use these.

    See https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-custom-format-strings.

    • Hi Mike
      Yes, I have written about the custom format string in Power BI a long time ago here.
      This article, however, wasn’t about that. It was about Power Query’s Date.ToText function.
      Cheers
      Reza

Leave a Reply

%d bloggers like this: