The Flair of a Culture (or Two)

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 23^rd—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)

Document Culture

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.

Display Culture

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.