Change type using locale: dates and numbers from other countries
Guide · By Tahir Nas · Examples run in our simulator · Published · Updated
Symptoms that point to the culture
| What you see | Likely cause |
|---|---|
| Some dates are errors, the rest look fine | Day-first dates read month-first: days above 12 fail |
| Dates look fine but months are wrong | Days 1–12 swapped silently with months |
| Amounts 100× or 1,000× too large or small | Decimal comma read as a thousands separator, or the reverse |
| Every amount is an error | Decimal comma file read with a decimal-point culture |
| Works on your PC, fails on a colleague's | No culture in the step, so each machine uses its own |
Pick the culture of the file
The culture describes the source, not you. A German export uses de-DE whatever your laptop is set to. The supported conversions in the simulator include en-US, en-GB, en-AU, en-CA, de-DE, fr-FR, es-ES and nl-NL.
let
Export = #table({"Invoice", "Date", "Net"}, {
{"F-01", "02.11.2026", "1.250,00"},
{"F-02", "28.10.2026", "89,90"},
{"F-03", "05.10.2026", "12.000,50"}
}),
Typed = Table.TransformColumnTypes(Export, {{"Date", type date}, {"Net", type number}}, "de-DE")
in
TypedResult
| Invoice | Date | Net |
|---|---|---|
| F-01 | 2026-11-02 | 1250 |
| F-02 | 2026-10-28 | 89.9 |
| F-03 | 2026-10-05 | 12000.5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Mixed cultures in one table
Sometimes dates come from one system and amounts from another. Convert column groups in separate steps, each with its own culture. One Changed Type step can only carry one culture.
let
Merged = #table({"Shipped", "Weight"}, {{"11/02/2026", "1,234.5"}, {"10/28/2026", "980"}}),
Dates = Table.TransformColumnTypes(Merged, {{"Shipped", type date}}, "en-US"),
Weights = Table.TransformColumnTypes(Dates, {{"Weight", type number}}, "en-US")
in
WeightsResult
| Shipped | Weight |
|---|---|
| 2026-11-02 | 1234.5 |
| 2026-10-28 | 980 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Don't do these
- Replace
,with.by hand. It breaks as soon as a thousands separator appears. - Split dates into parts with Text.Middle and rebuild them. A culture-aware conversion already does that, and handles one-digit days.
- Change your computer's regional settings to make a file work. The query still fails for everyone else.
Practise it in the simulator
Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. Free tasks need no account.
- 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~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.
Related pages
Lessons
More resources
Function reference
Sources
Microsoft's official documentation for the features and functions used on this page.
- Data types in Power Query (Concept, Microsoft Learn)
- Text/CSV connector (Concept, Microsoft Learn)
- Table.TransformColumnTypes (Function reference, Microsoft Learn)
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.