Skip to main content

Changing the Language of Calendar in M

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

Popular posts from this blog

Manipulating Grand Totals with ISINSCOPE in Power BI

ISINSCOPE function was introduced in with November 2018 Power BI update. We can say it's a very young player in the game, but it's definitely magic. It can be used to manage lots of issues related to hierarchies, but in this post, I'm going to focus on manipulating the grand total line with it.  This is my budget forecast table. The budget forecast that you see for every month is not the forecast for every month, but it's the forecast for the whole year. The budget forecast for 2020 has changed every month. That's why we have different numbers for every row. Below you see the table visual with the year-month column and a measure that shows the values for every month. The thing that I want you to pay attention to is the total line. I wanted to see the last month's value for the total line and this is the measure for that.  This measure calculates this: If the year-month column has one value then take the average of that value. If it doesn't have one value, li...

How to Add Flags to Power BI Reports

Adding flags to Power BI Reports might be easier said than done. If you have a dataset which contains data of various countries, adding country flags looks cool. The process to do it, on the other hand, might be compelling. Now I am going to share with you the obstacles I  have faced during the process and of course, how I overcame them. First I made an excel list of countries that my dataset has. Then I have downloaded to my computer all the flags I need from Wikipedia and converted them to Base64 link via an image encoder website  base64-image.de  and added those links to my excel file, which looks like this: After I added my excel file to Power BI, I converted the data category of URL column from 'uncategorized' to 'image URL', which might be a small step for you but it's a big step for the success of the flag operation. Then I created one to many relationship with DimLocation table and chose 'both' as filter direction, so that I can filter from both t...

Solving Data Type Conflicts in Power BI and SQL

Whether we like it or not, error messages are part of our lives. The other day, I faced one of those little horrid yellow messages. For me, it's impossible to stay chill when I see that message. For a second, I feel alarmed and panicked. Let me show you what I'm talking about. The first column is the customer code, quite self-explanatory. The Registration Date column shows the date when the customer registered to my shop. The First Sale Date column shows the date when the customer bought something for the first time. And this is what I'm trying to see in the Sales Day column: If Registration Date and First Sale Date are the same, meaning customer bought something at the same day customer registered to my shop, print  "First Day", otherwise show the data of First Sale Date column. But computer says no! It says "Expressions that yield variant data-type cannot be used to define calculated columns." If I need to translate it in simplified English, it means: ...