This blog is like my previous blog named Month and Year Combination in Power BI Axis/Slicer – DAX, runs on the similar lines. If you might have gone through the above-mentioned blog, you will likely understand the requirement and scenario (if not kindly go through that, however I will describe briefly here also).
Scenario:
Recently, we are asked to achieve the date axis with month and year in short form, such as
Aug-21, Aug 21. To get this as axis, a column should be present in the date dimension. Let us learn how to create this in Power Query and use.
My previous blog will solve the same requirement using the DAX and now we are going to solve the requirement using the Power Query in Power BI.
Sample Data in Power Query:
The above image shows the sample data of by existing date table in power query, now we need to create a new column using the M language.
Create required Month Year column:
Basic idea in DAX to create this column is to use the Format function, which is a text function, similarly we need some kind of format function in Power Query as well. Luckily, we have Date.ToText function, let us use this.
Syntax:
Date.ToText(date as nullable date, optional options as any, optional culture as nullable text) as nullable text
In simple words, it takes one parameter which is date, and it will convert to text (string) format of the date (based on locate setting). First optional parameter is format parameter, second optional parameter is for locale-based date conversion to text.
Example using the third parameter:
Its time to create the Month Year column, create the custom column as below,
Be careful, power query is case sensitive and for months we need to upper case (MMM), and for year we need to use lower case (yy).
On creating the Custom column named MonthYear we will see the date dimension as below,
Now, if we want to use this column in chronological order of time, then it will be difficult with out some index/code column for sorting. So, let’s create index/code column.
Creating Index/Code Column:
We use the similar date format function that we used above by adding the custom column to existing table.
Resultant custom column would be something like below with name MonthYearNumber,
Close and apply the power query window, which will make the data to load into BI, now we can sort the MonthYear column using the MonthYearNumber (you can check my previous mentioned blog here how to sort one column using the other).
Now, it is ready to use this column in the slicer, axis etc.
Thanks!
Srikanth Kotapati
Data Analyst
Addend Analytics