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
- the official documentation does provide a list of all available culture codes, but it is very hard to find
- 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
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…
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.
Francois meint
Thanks so much, this information was so hard to find elsewhere!
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
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!
Curt Hagenlocher meint
The documentation for Text.Lower, Text.Upper and Text.Proper has now been fixed.
Lars Schreiber meint
Hi Curt,
thanks for the information 🙂
Cheers,
Lars
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.
Lars Schreiber meint
Hey Jes,
that makes perfectly sense. Thanks for your comment and the enlightenment 🙂
Cheers,
Lars