Listing Windows Language Code Identifiers And Their Associated Date And Number Formats With M In Power BI/Power Query

In a comment on my blog post about international date and number formats and changing data types with the “using locale” option in Power Query/Power BI, Jan Karel Pieterse asked if there was any way to get a list of the thousand and decimal separators used for number formatting by each language and region. Since this is exactly the kind of geeky question that fascinates me I decided to write an M query to answer it and – for bonus points – to find the default date format used too.

To start off, I found a table of all Windows Language Code Identifiers on this page:

Of course this can be loaded into Power Query easily using the “From Web” source. After that it’s easy to add a column to the table that takes a sample date (March 22nd 2018) and number (one hundred thousand and one tenth) and converts it to text using the language code identifier on each row: the Text.From() function does this for dates, and for numbers you have to use Number.ToText() if you want to get thousand separators and decimal separators. There are a few minor problems to deal with, such as the fact that Power Query doesn’t know what to do with dates for the “Congo Swahili” language code identifier and some rows have multiple language tags, but nothing serious.

Here’s the full code:

//Sample dates and numbers to show
SampleDate = #date(2018,3,22),
SampleNumber = 100000+(1/10),
//MS web page with list of language tags
LocaleWebPage =
LocaleList = LocaleWebPage{1}[Data],
RemoveColumns =
{"Language", "Location (or type)", "Language tag"}
SplitColumn =
"Language tag",
{"Language tag"}
//Create example columns
DateExample =
each Text.From(SampleDate, [Language tag])
, Text.Type),
NumberExample =
each Number.ToText(SampleNumber,"N", [Language tag])
, Text.Type),
//Remove any rows containing errors
RemoveErrors = Table.RemoveRowsWithErrors(NumberExample)

Here’s some of the output:

So, if you’ve ever wondered how the Cornish speakers of south-west England like to format their dates or whether the Oromo speakers of Ethiopia use a comma or a full stop as a decimal separator, wonder no more. And if you are not interested in M at all and just want to download an Excel workbook with a list of all LCIDs and how numbers and dates are formatted for them, you can do so here.

5 thoughts on “Listing Windows Language Code Identifiers And Their Associated Date And Number Formats With M In Power BI/Power Query

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      That might be a bit more difficult… let me see what I can find.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.