Power QueryExercises

Convert dates and numbers with a locale

Use the source culture so day/month order and decimal separators are interpreted correctly.

~10 min read · intermediate · Module 3: Data types, locale and nulls

By Tahir Nas · Examples run in our simulator · Published · Updated

Before this: Choose types deliberately: identifiers are text

Text like 05/03/2026 is ambiguous: 5 March in the UK, May 3 in the US. 1.234,56 is one thousand two hundred thirty-four point five six in Germany, and something else entirely to an en-US parser. The text doesn't say which convention it uses, so you have to.

The culture argument

Table.TransformColumnTypes takes an optional culture as its last argument. It controls how text is read, not how values are displayed afterwards. Use one step per source convention:

let
    Source = #table({"ClaimDate", "Amount"}, {{"05/03/2026", "1.234,56"}, {"28/02/2026", "89,90"}}),
    Dates = Table.TransformColumnTypes(Source, {{"ClaimDate", type date}}, "en-GB"),
    Amounts = Table.TransformColumnTypes(Dates, {{"Amount", type number}}, "de-DE")
in
    Amounts

Result

ClaimDateAmount
2026-03-051234.56
2026-02-2889.9

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Dates are shown in ISO form (year-month-day) so there's no ambiguity on this page.

In the editor this is right-click → Change Type → Using Locale…. Without a culture, the host uses the computer's regional settings. A query can then work on your laptop and produce swapped dates on a colleague's.

How the wrong culture shows up

Convert the same day-first dates with en-US and two things happen. Days 1–12 are silently swapped with the month, and days above 12 become errors because there's no 28th month:

Table.TransformColumnTypes(
    #table({"ClaimDate"}, {{"05/03/2026"}, {"28/02/2026"}}),
    {{"ClaimDate", type date}},
    "en-US"
)

Result

ClaimDate
2026-05-03
Error: DataFormat.Error: We couldn't parse the input provided as a Date value.

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The silent swap is the dangerous half: 5 March becomes 3 May with no error at all. If a date column has some errors after conversion, suspect the culture before blaming the data.

Converting single values

Inside custom columns, the conversion functions take a culture too:

#table({"Function", "Result"}, {
    {"Date.FromText en-GB", Date.FromText("05/03/2026", [Culture = "en-GB"])},
    {"Number.FromText de-DE", Number.FromText("1.234,56", "de-DE")}
})

Result

FunctionResult
Date.FromText en-GB2026-03-05
Number.FromText de-DE1234.56

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Where to find this in Excel

Excel for Windows
Right-click the column header > Change Type > Using Locale
What you see
The Change column type with locale dialog asks for the target type and the locale the text was written in.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Data types in Power Query (Microsoft Learn).

Common mistakes

  • Converting day-first dates without a culture, so days 1–12 swap silently and later days become errors.
  • Replacing decimal commas with dots instead of converting with the source culture.
  • Using the culture of the reader (your machine) instead of the culture of the file.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: right-click a column → Change Type → Using Locale… writes the culture into Table.TransformColumnTypes.
  • The file-level Locale setting (Excel: Query Options → Current Workbook → Regional Settings; Power BI: Options → Current File → Regional Settings) changes the default for conversions without an explicit culture. An explicit culture in the step is more portable.
  • The simulator supports en-US, en-GB, en-AU, en-CA, de-DE, fr-FR, es-ES and nl-NL; other cultures raise UnsupportedFeature rather than guessing.
Functions in this lesson:Table.TransformColumnTypesDate.FromTextNumber.FromText

Next step

Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's free, with no account needed.

Practise: Convert day-first dates with the right locale (consultancy)

Practice this

Each exercise runs in the browser and is checked against fresh data, not just the example.