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:

let
//Sample dates and numbers to show
SampleDate = #date(2018,3,22),
SampleNumber = 100000+(1/10),
//MS web page with list of language tags
LocaleWebPage =
Web.Page(
Web.Contents(
"https://msdn.microsoft.com/en-us/library/cc233982.aspx"
)
),
LocaleList = LocaleWebPage{1}[Data],
RemoveColumns =
Table.SelectColumns(
LocaleList,
{"Language", "Location (or type)", "Language tag"}
),
SplitColumn =
Table.SplitColumn(
RemoveColumns,
"Language tag",
Splitter.SplitTextByAnyDelimiter(
{",","or"},
QuoteStyle.Csv
),
{"Language tag"}
),
//Create example columns
DateExample =
Table.AddColumn(
SplitColumn,
"Date",
each Text.From(SampleDate, [Language tag])
, Text.Type),
NumberExample =
Table.AddColumn(
DateExample,
"Number",
each Number.ToText(SampleNumber,"N", [Language tag])
, Text.Type),
//Remove any rows containing errors
RemoveErrors = Table.RemoveRowsWithErrors(NumberExample)
in
RemoveErrors

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.