THE SELF-SERVICE-BI BLOG

Wir lieben Microsoft Power BI

Technical Topics (english) /

Culture codes in M functions

At the time of writing (October ’18) there are 35 functions in M that accept a parameter called culture. The functionality of this parameter is relatively easy to understand. Let’s use the function Date.DayOfWeekName(#date(2018,1,1), "zh-HK") as an example. If you run this function on your computer, you will get the day name of January 1, 2018 (which is Monday) in Chinese language (which is  星期一), regardless of the culture set on your operating system. Because this parameter is mostly optional (except for function Comparer.FromCulture()) you can leave it away. In this case, the culture that is set on your operating system is automatically used.

Update (10th of November 2018): As you can read in the comment from Curt Hagenlocher below, who works for the Power Query team in Redmond, I wasn’t very precise with my last remark. If you want to learn about ‚document culture‘, read his comment in the comments below. Thank you Curt, for your correcting 🙂

If you want to use Power Query to determine which culture is set on your PC, you can use the following function: Culture.Current.

The reason why I am writing this article is not that the way culture works is so difficult to understand, but that

  1. the official documentation does provide a list of all available culture codes, but it is very hard to find
  2. for some functions in the official documentation of the specific function, the parameter culture is not even listed

Here comes a list of functions, which can handle the parameter culture. For the red ones the parameter culture is not mentioned in the official documentation:

  • 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

Thanks to Jes (see the first comment below) I now know, why it makes sense, to have the culture parameter inside the functions  Text.Lower, Text.Proper and Text.Upper functions.

You can find a list of officially supported culture codes here. You have to scroll a bit down to the end. My thanks go to Sergei Baklan, who helped me to find this list 🙂

Regards from Germany,

Lars

Leser-Interaktionen

Kommentare

  1. Steven Bitaxi meint

    Thank you, Lars. Your post about it was much easier to find than to locate the information in the Microsoft Docs. Really appreciate that you blogged about this.

    • Lars Schreiber meint

      Hi Francois ,

      this is why I wrote this post. I coulnd’t find any other resource, so I wrote my own documentation and hoped it would help others as well 🙂

      Cheers,
      Lars

  3. Curt Hagenlocher meint

    „In this case, the culture that is set on your operating system is automatically used.“

    Actually, it’s a little more complicated than that. The culture is stored alongside the queries in the document, so if I create a document on my work machine and send it to you, the default culture will be „en-US“ no matter what culture your machine is set to. We call this the „document culture“, and you can change it in Power BI Desktop by going to the „Regional Settings“ in the Current File settings of section of the Options dialog. There’s similar functionality in Excel as well.

    Grüße aus Redmond!

  4. Oxenskiold meint

    Hi Lars,
    regarding Text.Lower, Text.Proper and Text.Upper and the culture parameter. Different OS-cultures have different rules for which lower case letter corresponds to which upper case letter and vice versa. Some do not have the concept of upper- and lower case letters at all. I believe Hebrew and Arabic are two of them. You being in Germany, the German letter ‚ß‘ (small letter sharp s (DEC-223)) in a string ‚groß‘ would probably be upper cased to ‚GROSS‘ using the German culture. Using the Danish culture it would be ‚GROß‘. Being a little bit lazy I didn’t change the culture of my computer to German to test this so I presume it is like this ( ‚groß‘ to ‚GROSS‘), but even if not you will be able to find many such examples.

    Bets regards Jes.