Read dd/mm/yyyy dates correctly in Power Query with en-IN locale

How the problem shows up

An invoice export from an Indian billing system writes dates as 03/04/2026, meaning 3 April. The analyst loads it into Power BI, clicks the column header, picks Date, and sees a handful of errors. The obvious response is to remove the errors, and the monthly totals then look almost right. They are not. Every surviving date with a day of 12 or below has been read as month/day.

Whether the plain Date type goes wrong depends on the locale in effect for that file or machine. That is why a report that works on one analyst's laptop can break when someone else refreshes it, or when it is scheduled through a gateway configured differently.

What the wrong locale does, row by row

The Python below reads ten synthetic dates both ways, to make the damage visible before touching Power Query. Two of them are five-digit numbers: a CSV opened in Excel and saved again often turns some dates into Excel date serials.

from datetime import datetime, timedelta
from collections import Counter

# Synthetic invoice dates exported by an Indian system as day/month/year text,
# plus two values that arrive as Excel serial numbers after someone opened and saved the CSV.
raw = ["03/04/2026", "15/04/2026", "30/04/2026", "05/05/2026", "12/05/2026",
       "21/05/2026", "02/06/2026", "11/06/2026", "46204", "46210"]

def read_as(text, fmt):
    try:
        return datetime.strptime(text, fmt).date()
    except ValueError:
        return None

def from_serial(text):
    """Excel's 1900 date system: serial 1 is 1 January 1900; offset 1899-12-30 absorbs the leap-year quirk."""
    return (datetime(1899, 12, 30) + timedelta(days=int(text))).date() if text.isdigit() else None

us = [read_as(t, "%m/%d/%Y") for t in raw]     # what a US-locale type change does
india = [read_as(t, "%d/%m/%Y") or from_serial(t) for t in raw]

for t, a, b in zip(raw, us, india):
    print(f"{t:>11}  en-US: {str(a):>10}   en-IN: {b}")

# The damage a wrong locale does is not only errors: some rows silently swap day and month.
swapped = sum(1 for a, b in zip(us, india) if a and b and a != b)
failed = sum(1 for a in us if a is None)
print("\nwrong locale -> silently swapped:", swapped, "| errors:", failed)
print("months after correct parse:", Counter(d.strftime("%b") for d in india))
assert swapped == 4 and failed == 5 and all(india)

Five rows fail under month-first reading: the three with a day above 12 and the two serials. Four rows succeed with the wrong date; 12/05/2026 becomes 5 December instead of 12 May, which moves that invoice from May sales into December. Only 05/05/2026 is correct, by coincidence. Correctly parsed, the ten rows fall in April to July.

The fix in Power Query

For a clean column of day/month/year text, the interface route is enough. Right-click the column, choose Change Type > Using Locale, set the data type to Date and the locale to English (India). The step that appears is:

= Table.TransformColumnTypes(#"Promoted Headers", {{"inv_date", type date}}, "en-IN")

The third argument is the culture. It is stored in the step, so the query now behaves the same on every machine.

When the column also holds Excel serials, or a mix of formats, parse row by row and keep track of what happened, instead of forcing one type conversion over everything:

let
    Source   = Csv.Document(File.Contents("C:\Data\invoices.csv"), [Delimiter = ",", Encoding = 65001]),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Parsed   = Table.AddColumn(Promoted, "InvoiceDate", each
                 let t = Text.Trim([inv_date]) in
                 try Date.FromText(t, [Format = "dd/MM/yyyy", Culture = "en-IN"])
                 otherwise try Date.From(Number.From(t))
                 otherwise null, type date),
    Flagged  = Table.AddColumn(Parsed, "DateSource", each
                 if [InvoiceDate] = null then "UNPARSED"
                 else if Text.Contains([inv_date], "/") then "text dd/MM/yyyy"
                 else "Excel serial", type text)
in
    Flagged

Date.FromText with an explicit Format reads exactly that pattern, and Culture covers month names if the format uses them. Date.From on a number treats it as a date serial in the same system Excel uses, so 46204 becomes 1 July 2026. Anything that neither reading accepts becomes null with the flag UNPARSED, and the original text column is kept beside it.

Checks after the change

Count by source. Group by DateSource and look at the counts. A sudden batch of Excel serials usually means someone opened and re-saved the export; ask for the original file.

Check the range. Take the minimum and maximum of the parsed column. For an April export, anything outside April is a question to answer before the report goes out.

Look at the day distribution. If the day-of-month values run only from 1 to 12, the column was probably read month-first somewhere upstream, before it reached you.

Never drop the error rows as a first step. Errors are the visible part of a locale problem; the swapped dates are the invisible part. Fix the locale first, then see whether any errors remain.

Stop it at the source when you can

If the export can be configured, ask for ISO dates (2026-04-03). They are unambiguous in every locale and sort correctly as text. Where that is not possible, document in the query itself which culture it expects, with a comment line such as // source writes dd/MM/yyyy, so the next person to edit it does not "fix" the step back to the plain type.

The same export usually needs its month columns turned into rows next; see unpivot April–March month columns in Power Query. Power Query steps that hold up on any machine, and the habit of checking what a type change actually did, are part of the data preparation work in our Data Analyst course.