Power Query Change Type Using Locale: Fix Dates and Decimal Commas

Identify the convention before touching the query

This walkthrough targets Microsoft 365 Excel for Windows and the desktop Power Query Editor. The synthetic export intentionally combines day-first dates with German-style amount text. A real system might export both columns using one convention; it might also obtain them from different upstream services. Treat the convention as a property of each field, not evidence of where the person opening the workbook lives.

Ask which date R1 represents and verify the answer against a record with an unambiguous day, such as 25/12/2026. R2 tells us that a month-first parser cannot consistently explain this file. R1 alone cannot settle the issue. Do the same for amounts: the agreed value of 1.234,50 is one thousand two hundred thirty-four and fifty hundredths. Write these decisions beside the query before testing conversions.

Import the raw file without losing the evidence

Save locale-input.csv and use Data > From Text/CSV, then Transform Data. Confirm comma is the file delimiter: quotation marks protect the comma inside amount_text. The field separator and the decimal separator perform different jobs. Changing the file delimiter to semicolon would not repair this comma-delimited sample.

Inspect Applied Steps before adding a correction. If an automatic Changed Type step has already interpreted date_text or amount_text, go to the preceding text step and remove or edit that early conversion. Merely changing an incorrectly parsed date back to text does not reconstruct its original characters. Preserve the CSV and duplicate the two raw columns if the final report needs both original and converted values.

Apply a locale separately to the two columns

Use the original text columns for the following actions. For R4, inspect whether the blank fields arrived as null or as empty text; this example treats either as missing before conversion. It does not treat the literal strings NA or unknown as a missing-value agreement.

1. Keep row_id as Text. Duplicate date_text and amount_text, naming the copies date and amount so the original strings remain available during review.
2. For the date copy, normalize empty text to null if needed. Right-click its header, choose Change Type > Using Locale, select Date and English (United Kingdom), then confirm.
3. For amount, apply the same missing-value rule. Choose Change Type > Using Locale with Decimal Number and German (Germany). Do not apply the date locale to this column just to keep the settings identical.
4. Inspect R1, R2 and R3, including their day and month components. Remove the original text columns only in the final presentation query after comparison.
5. Sort by row_id, then Home > Close & Load to a new worksheet. Retain the query and refresh it from the same unchanged input before trying another export.

Run a complete query and keep the original text

For a repeatable alternative to the menu steps, download locale-conversion.pq and locale-input.csv. Create a Blank Query, open Advanced Editor, paste the query and set FilePath to the CSV on your computer. Only this local file is read. The result retains date_text and amount_text beside the converted date and amount, so you can inspect what changed before removing the originals.

The date helper accepts null as missing, parses with en-GB and compares the result formatted as dd/MM/yyyy with the original text. This round-trip check intentionally rejects 3/4/2026 under a two-digit day/month contract. It also keeps 31/02/2026 as an error. Change the contract explicitly if your exporter permits a different format; do not remove failing rows to make totals look correct.

Version finding: on our Excel 16.0 build 5569.0, the modern Date.FromText options record produced a Record-to-Text conversion error. Microsoft documents that record syntax for current engines. The download uses the older culture-text argument plus a separate exact-format check, which executed successfully on the tested build. This is a compatibility observation, not a claim that every Excel installation has the same engine.

let
    FilePath = "C:\CSVInput\locale-input.csv",
    Raw = Csv.Document(File.Contents(FilePath), [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
    Headers = Table.PromoteHeaders(Raw, [PromoteAllScalars=true]),
    EmptyToNull = Table.TransformColumns(Headers, {
        {"date_text", each if _ = "" then null else _, type nullable text},
        {"amount_text", each if _ = "" then null else _, type nullable text}
    }),
    ParseDate = (value as nullable text) as nullable date =>
        if value = null then null else
        let Parsed = Date.FromText(value, "en-GB")
        in if Date.ToText(Parsed, "dd/MM/yyyy", "en-GB") = value then Parsed
           else error "Expected an exact dd/MM/yyyy date: " & value,
    Dates = Table.AddColumn(EmptyToNull, "date", each ParseDate([date_text]), type nullable date),
    Amounts = Table.AddColumn(Dates, "amount", each Number.FromText([amount_text], "de-DE"), type nullable number),
    Sorted = Table.Sort(Amounts, {{"row_id", Order.Ascending}})
in
    Sorted

Distinguish an error from a silent wrong answer

A conversion error is useful evidence that a value does not match the selected parser. A successful conversion proves less: both interpretations of 03/04/2026 are legitimate dates. Validate at least one ambiguous value against its intended meaning, rather than counting errors alone. The expected file makes R1 a deliberate check of this failure mode.

For a fixed export specification, Date.FromText can accept an explicit Format and Culture. For example, a column contract of dd/MM/yyyy deserves a format-specific conversion rather than best-effort interpretation. Number.FromText likewise accepts a culture. These functions are useful when the graphical transformation is too broad; they cannot discover the correct business meaning of an ambiguous string.

Keep missing, zero, and rejected values separate

R2 amount zero is a recorded numeric value. R4 amount is missing, so the report has three recorded amounts across four source records. Filling R4 with zero would leave the total unchanged while changing that completeness count. Preserve this distinction if later calculations average amounts or measure missing data.

Add a test row R5 with date_text 31/02/2026 in a copy of the input. It is an invalid calendar date under the declared convention and should be investigated. A locale setting cannot make it valid. Keep an exception query or record the error; do not use Remove Errors merely to get a clean-looking report. The production extract should not quietly lose its rejected records.

Make the next refresh repeatable

Keep a short import contract stating the source delimiter, encoding, date pattern, numeric culture, and missing-value rule. On the next export, compare those details before assuming the query remains valid. A new column label or a source system language change can require a different transformation even when the workbook itself has not changed.

If one column genuinely contains several conventions, split it using reliable provenance such as source_system and apply a parser to each subset. Do not guess by trying one culture and falling back to another: ambiguous values may succeed on the first attempt with the wrong interpretation. Combine the validated subsets afterwards, retaining an origin column so the rule remains auditable.

What we ran in Excel, and how to reproduce it

On October 5, 2026, we executed the downloadable M query through the native Power Query provider in Excel 16.0 build 5569.0. All four rows remained: R1 became April 3, 2026 and 1234.50; R2 retained a numeric zero; R3 retained -25.75; R4 retained missing values. The three recorded amounts totaled 1208.75. These are engine execution results, separate from our earlier independent fixture calculations.

Three deliberate changes were tested separately: an impossible date, a nonnumeric amount and a date that violates the exact format. Each remained visible as an error on its source row. Use locale-native-checks.csv as a test checklist. In Power Query, keep an unchanged source copy, make one change, and use Keep Errors on the affected converted column to inspect the rejected row; restore the file before the next case.

The menu clicks, dialogs, Microsoft 365 online and Mac versions were not exercised by this native-provider test. Worksheet formatting can differ from the ISO dates in the expected CSV. Compare underlying dates and numbers, not just their appearance.