First of all, why do we need to change the language of the calendar? If we're working on a report, which is going to have multiple languages for different users, changing the language of the calendar is a must and there are 2 ways to do that. The first one is the one that I don't recommend, changing the current file's regional settings from options and settings menu.
This is the option to change the file's language and it's quite handy when we need to do that. But what if we only want to change the calendar's language, not the whole file. And what if we want to have multiple language options for our calendar. In that situation, this menu is not going to be much of a help. So how can we do it? First, let's look at our date table code in M language.
As you see, in lines 12 and 14. Date.ToText function is used to create Monthname and Dayname columns. Date.ToText function's name is quite self-explanatory. It converts date numbers to text. If you'd like to sound cool, you can say "Returns a textual representation of date" like Microsoft does. Here comes the syntax :
Date.ToText(date as nullable date, optional format as
nullable text, optional culture as nullable text) as nullable text.
We see that it takes 3 parameters. The last one, which is optional like the previous one, is where we can define the language of the text. We can choose any language we want, as long as that language is supported by Power BI. On this occasion, I am going to write there "es-es" which stands for Spain Spanish. This is how it looks after adding the language parameter.
What if I'd like to change them in one step to any language I prefer? To be able to do that, we need to create a variable. After that, we need to add that variable to the 'culture as nullable part' of the Date.ToText formula. I have created the variable in line 1 and put it in lines 12 and 14.
After I clicked okay, this is the screen I got:
Now I can text any language I prefer to the language parameter part. With this little trick, we can create a calendar with month name and day name with the language we choose. Before I finish, I also like to add that there are 35 M functions that accept culture as a parameter. Date.ToText is just one of them. Here is the full list:
- Byte.From
- Comparer.FromCulture
- Currency.From
- Date.DayOfWeekName
- Date.From
- Date.FromText
- Date.MonthName
- Date.ToText
- DateTime.From
- DateTime.FromText
- DateTime.ToText
- DateTimeZone.From
- DateTimeZone.FromText
- DateTimeZone.ToText
- Decimal.From
- Double.From
- Int16.From
- Int32.From
- Int64.From
- Int8.From
- Number.From
- Number.FromText
- Number.ToText
- Percentage.From
- Single.From
- Table.TransformColumnTypes
- Text.Format
- Text.From
- Text.Lower
- Text.Proper
- Text.Upper
- Time.From
- Time.FromText
- Time.ToText
- Value.FromText
And here is the link for the list of languages and their abbreviations that you can use in the culture parameter. Click here
Comments