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
AmountsResult
| ClaimDate | Amount |
|---|---|
| 2026-03-05 | 1234.56 |
| 2026-02-28 | 89.9 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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
| Function | Result |
|---|---|
| Date.FromText en-GB | 2026-03-05 |
| Number.FromText de-DE | 1234.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.
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.
- intermediate~8 minFree
Convert day-first dates with the right locale (consultancy)
Claim dates are written 05/03/2026 meaning 5 March. Convert them to real dates using the en-GB culture.
- intermediate~8 minPro
Convert day-first dates with the right locale (school)
Claim dates are written 05/03/2026 meaning 5 March. Convert them to real dates using the en-GB culture.
- intermediate~7 minFree
Read amounts written with a decimal comma (consultancy)
A German export writes 1.234,56 for one thousand two hundred thirty-four point five six. Convert it with the de-DE culture.
- intermediate~7 minPro
Read amounts written with a decimal comma (school)
A German export writes 1.234,56 for one thousand two hundred thirty-four point five six. Convert it with the de-DE culture.