Power QueryExercises

Change type using locale: dates and numbers from other countries

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

Jump to the practice tasks

Symptoms that point to the culture

What you seeLikely cause
Some dates are errors, the rest look fineDay-first dates read month-first: days above 12 fail
Dates look fine but months are wrongDays 1–12 swapped silently with months
Amounts 100× or 1,000× too large or smallDecimal comma read as a thousands separator, or the reverse
Every amount is an errorDecimal comma file read with a decimal-point culture
Works on your PC, fails on a colleague'sNo 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
    Typed

Result

InvoiceDateNet
F-012026-11-021250
F-022026-10-2889.9
F-032026-10-0512000.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
    Weights

Result

ShippedWeight
2026-11-021234.5
2026-10-28980

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.

Lessons

More resources

Function reference

Error fixes

Sources

Microsoft's official documentation for the features and functions used on this page.

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.