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.
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;
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;
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.
|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|
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;
|D||long date||Wednesday, 1 January 2020|
|ddd||day name three-characters||Wed|
|dddd||day name full||Wednesday|
|m or M||day and month combination||1 January|
|MM||month number two-characters||01|
|MMM||Month name three-characters||Jan|
|MMMM||Month name full||January|
|y or Y||Month and year combination||January 2020|
|yy||two characters year number||20|
|yyy or yyyy||year number four characters||2020|
|short date and time||1/01/2020 12:00:00 am|
|f||long date and short time||Wednesday, 1 January 2020 12:00 am|
|F||long date and long time||Wednesday, 1 January 2020 12:00:00 am|
|g||short date and short time||1/01/2020 12:00 am|
|G||short date and long time||1/01/2020 12:00:00 am|
|o or O||datetime with miliseconds||2020-01-01T00:00:00.0000000|
|r or R||Date and time with timezone||Wed, 01 Jan 2020 00:00:00 GMT|
|s||short date time||2020-01-01T00:00:00|
|T||time with seconds||12:00:00 am|
|u||short datetime and timezone||2020-01-01 00:00:00Z|
|tt||am or pm||am|
|hh||hours (12-hours basis)||2|
|HH||hours (24-hours basis)||14|
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
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;
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.
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;
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?
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.
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.
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.