Numbers and dates are formatted differently in different parts of the world. How are these cultural differences handled in the realm of Power Query? Turns out, arguably, there can be not just one—but two—sets of rules in play.
In the M language, numbers and date/time-based values are natively stored in culture-agnostic formats. It doesn’t matter what part of the world you’re in or how it formats values, when
#date(2023, 1, 23) is evaluated, Power Query understands that the referenced year is 2023, the month is 1 and the day is the 23rd—and it maintains this understanding throughout the value’s lifetime. Similar holds true with the other date/time types, as well as with numbers.
On the other hand, when converting values of these types to or from text, culture does come into play—but which culture?
Number.ToText(123456.78, "n") // outputs: // 123,456.78 (if the culture is en-US) // 123.456,78 (if the culture is es-ES) // 123 456,78 (if the culture is se-SE)
It may seem like your local computer’s culture is used, but that’s not quite correct—and thankfully, not!
Imagine that the local system’s regional settings were always used. You send a file to a colleague in a different part of the world. If they refresh its Power Query, they may receive back different results from what you received because of their computer’s different locale setting. Ouch!
Instead, each Power Query file has a document locale setting (a.k.a. document culture setting) which defines the default locale used when processing the M code in that file.
When you create a new file, this setting is defaulted to your local computer’s culture, then remains unchanged unless you manually modify it.
This gives you the convenience of PQ’s culture matching your system’s local culture by default (which is probably what most people want) while at the same time assuring consistent behavior if the file is sent to others around the globe (because their processing of the PQ expressions in your file will use that file’s document culture, not their local culture).
Jumping to the cloud: Nothing changes for files that you upload. Power Query in the Power BI Service still honors the document culture.
But not everything in the Power BI Service is uploaded. What about Power BI data flows, which are created in the cloud? They, too, have a document locale setting, which you can adjust at your pleasure. (At data flow creation, this setting is defaulted to a value derived from your web browser’s regional configuration.)
Overriding the Default
If you’re using a standard library conversion function and you want it to reflect a locale other than the default (i.e. other than the document culture), simply set the function’s optional culture argument or option record value, as appropriate, to the desired culture.
Number.ToText(123456.78, "n", "en-US") // 123,456.78 Number.ToText(123456.78, "n", "es-ES") // 123.456,78 Date.FromText("1/3/23", [Culture="en-US"]) // #date(2023, 1, 3) Date.FromText("1/3/23", [Culture="en-GB"]) // #date(2023, 3, 1)
Best Practice Tip: Unless the output you’re generating needs to reflect multiple cultures, you shouldn’t need to use code to specify the culture. If you find that you’re explicitly specifying culture values in code and those values frequently reference the same culture, consider whether the document culture is set correctly. Adjust the document culture to match the (main) culture that should be used, reserving explicit culture specifications for exceptional cases.
Up until now, we’ve been talking strictly in terms of Power Query proper: principally, the mashup engine, the standard library and M code you write.
There is, however, another culture that can come into play. This one affects how you see values. Not how M code converts between M’s native, culture-agnostic data types—but rather how those native, culture-agnostic values are displayed by the host application’s user interface.
Say you evaluate the following expression. What’s output is a value of type date, which is culture-agnostic—but how is this native, non-textual value rendered to screen so you can see it?
#date(2023, 1, 23
That’s up to the host application. Power Query provides it with the native, culture-agnostic value and the host decides if/how to display it.
The desktop-based Query Editor uses your local culture. Not Power Query’s document culture, but your local system’s culture. If you send the file to a colleague somewhere else in the world, and they work with it in Query Editor, they may see a different screen rendering of the value—whatever would be appropriate for their culture.
In the Power BI Service’s online query editor (i.e. data flow’s query editor), the UI piggybacks off of the document culture. So, for data flows, Power Query’s document locale affects both the conversions done in M code and how the host application behaves (specifically, how it previews values in its query editor).
For other host applications, whatever rules they choose to use apply. For example, Power BI has a locale that is set when the dataset is created and also can factor in the local operating system’s settings.
Thanks so much for this article. I am a bit confused. From the way I read the article, it would seem that all of the display mechanics are taken care of “behind the scenes”. I am sure I am mis-reading b/c if that were the case, what would be the need for “using locale” in the data type drop
down menu? I have always taught my students that if their file is going to be shared to another country, then set the data type for currency, numbers and dates using locale ao that they will display appropriately no matter where the file is viewed. Is this not needed? Thank for any clarification you can provide.
The document locale sets the default locale used for the conversions performed by M standard library functions. If your file only targets one locale, you can simply set that locale to be the document locale. Since the various conversions will follow this default by default, there’s no need to explicitly use “using locale” on each.
The main reason to consider using the “using locale” option is if your file’s output should reflect multiple locales. Say, one of the English-speaking locales is your default but you want to output a date table with both English and Spanish month name columns. For the English column, you could simply render the date to text, since you’ve set English to be used by default for these kinds of conversions. For the Spanish column, you’d do the same conversion, but this time you’d need to explicitly specify a Spanish-speaking culture to obtain output in that language.
OK, so let me see if I understand this correctly. If I want to display a column using a different locale’s convention then I would use the “Using Locale” feature. But I DON’T need to set the “Using Locale” to make sure that a date column displays properly if the report is opened in another country (e.g. Germany)?
I guess the reason I am confused is that once, when I was teaching a class, I had students from Germany who said that the fields with dates and currency in them did NOT display properly for them. So a number we (in the US) had entered as $1,010.55 (just as an example) it did NOT display as 1.010,55 as it should have to match German conventions. The number retained the US formatting which of course is a completely different value. I am afraid I am still bewildered….
What do you mean by display? Displayed to screen or convert to a text value? 🙂
“Using locale” and the (default) document locale setting affect M standard library functions that convert to or from text. Examples:
Separate from the proceeding, in the user interface:
numbervalues will be displayed based on the host system’s locale setting (Power BI Desktop/Excel) or the document locale setting (Power BI data flows).
Say a table column whose type is set to
Currency.Typeis previewed in Power BI Desktop’s Query Editor. How that value is rendered to screen (formatted) will depend solely on the system’s locale settings.
On the other hand, if you add a column to the above table that is defined as
Text.From([column with currency value]), the resulting text value that’s generated will be formatted based on the document locale setting. If you don’t like the locale used, you can either change the document locale setting or you can set
Text.From‘s optional second argument to specify the desired locale.
Does that help?