In Power Query, right-click the date column, choose Change Type, then Using Locale, set the type to Date and the locale to English (India). The conversion no longer depends on the PC's region, so it gives the same dates on a colleague's computer.
Why does the locale matter?
When Power Query loads text, it adds a Changed Type step that detects types automatically, using the language and region of the machine that built the query. On a US-style setup, text such as 05/01/2024 is read month first and 18/02/2024 fails. A locale-based conversion states the rule in the query instead of borrowing it from the PC, which is what makes the file portable.
The steps
- Load your data with Data, then From Table/Range, or From Text/CSV and choose Transform Data.
- If an automatic Changed Type step already converted the date column, delete that step in the Applied Steps list on the right. Otherwise the damage is done before your step runs, and day and month are already swapped.
- Right-click the date column header, choose Change Type, then Using Locale.
- In the box, set Data Type to Date and Locale to English (India).
- Press OK, then Home, Close and Load.
The step Power Query writes behind the scenes looks like this, with your own table and column names:
- =Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-IN")
The last argument is the culture code. Anyone who opens the query sees en-IN written there, so the intention is plain.
Why does refresh keep working?
The locale is stored inside the query. When you press Refresh All, or when the file is opened by someone with a different regional setting, Power Query repeats the same steps with the same culture. Next month, paste fresh statement rows into the source table, refresh, and the dates convert the same way. The Windows region plays no part in it.
Can I set a default for the whole workbook?
Yes. In the Power Query editor choose File, Options and settings, Query options. Under Current Workbook there is a Regional Settings section with a locale list, and a Data Load section that controls type detection. For files that come from banks, many people prefer to turn type detection off for unstructured sources and set each type themselves. Exact names vary a little by version, so look for the nearest equivalents.
| Situation | Locale route | Result |
|---|---|---|
| Text dates dd/mm/yyyy | Change Type, Using Locale, English (India) | Day read first |
| Text dates mm/dd/yyyy | Using Locale, English (United States) | Month read first |
| Month names such as 05-Jan-2024 | Either works | Month name is unambiguous |
Also refresh once on purpose. Paste a few new rows into the source, press Refresh All, and confirm the new dates convert in the same way, because the refresh is the moment a locale mistake would show up.
How do I test it?
- Look at a row with a day above 12, such as 18/02/2024. If it converts, the day-first rule is working. If it shows an error, the locale was not applied.
- Look at a row with a day below 13, such as 05/01/2024, and confirm it says 5 January, not 1 May.
- Filter the column for errors. An empty result is what you want.
The same Using Locale step works on amounts. Text such as 1,25,000.00 in the Debit column converts to a number when you choose Decimal Number with English (India), because the locale tells Power Query how this country groups digits. Test it on your largest amount before loading.
What it does not fix
A locale step assumes your text really is dd/mm/yyyy. If a bank mixes formats in one column, or the cell has hidden spaces, you still get errors. Use Transform, then Format, then Trim for the spaces, and keep a plain count of rows loaded against rows expected on the sheet next to the table.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


