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 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)

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!

Power BI Desktop's regional settings showing its document locale setting
Document locale setting in Power BI Desktop. Excel’s UI for this setting is similar though not exactly the same.

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.

Power BI Service's data flows query editor project options showing its document locale setting

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).

Tooltip in Power BI Desktop indicating that the local operating system's settings can affect display formatting

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.

2 thoughts on “The Flair of a Culture (or Two)

  1. Jessica Jolly

    Hi Ben—
    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.

    Reply
    1. Ben Gribaudo Post author

      Hi Jessica,

      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.

      let
          Source = #table(type table [Date = date], {{#date(2023, 1, 25)}}),
          #"Added Month Name (English)" = Table.AddColumn(Source, "Month Name (English)", each Date.ToText([Date], [Format="MMMM"]), type text),
          #"Added Month Name (Spanish)" = Table.AddColumn(#"Added Month Name (English)", "Month Name (Spanish)", each Date.ToText([Date], [Format="MMMM", Culture="es-ES"]), type text)
      in
          #"Added Month Name (Spanish)"
      
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *