The string 03/04/2024 can look settled while describing two different dates. Read as month/day/year, it is March 4;read as day/month/year, it is 3 April. A tidy imported column can therefore remain wrong for the source even when no error message appears. The useful question is not only whether software accepted the text, but which day, month and year it made from it.
Keep the source text beside both readings
Use 03/04/2024 as an invented value, not a record from a real city file. Under an explicitly declared US month/day/year interpretation, its components are year 2024,month 3,day 4. Under a UK day/month/year interpretation, they are year 2024,month 4,day 3. Both readings use valid calendar components, so validity alone cannot decide the intended one.
For a city notice, the difference could move the record into another month. That consequence follows from the two worked readings; it is not a claim that an actual council notice was misimported. The missing evidence is the source's intended convention or another authoritative statement of the date.
Keep the original string available while resolving that evidence. If the source explicitly establishes 3 April 2024,an unambiguous year-month-day note can read 2024-04-03. If it establishes March 4,the corresponding note is 2024-03-04. Rewriting the ambiguous string in that form is useful only after deciding which date the source actually means.
Power Query names the interpretation setting
Microsoft's Data types in Power Query documentation, updatedAugust 14,2026,explains that locale governs interpretation of text during type conversion. For Power Query Desktop it describes an operating-system regional default and a current-file Regional settings override. The documentation also describes Change type,Using locale as a way to choose a data type together with the intended locale.
Its worked example uses UK-formatted dates with a US locale and explains an error involving a day value that cannot be a month. Our 03/04 case is different: both proposed month values are within the calendar. A missing error does not establish that the source's intended convention was preserved.
This is a reading and conversion plan based on the documented behavior, not a report of running Power Query or a promise that every edition shows identical menus. For an actual import, use the instructions for the real Power Query experience and keep the chosen locale with the transformation record.
Verify components rather than a familiar-looking display
Microsoft recommends checking a converted date by extracting its year, month and day into additional columns. Apply that principle to the intended 3 April example: the desired verification note is 2024,4,3. A result of 2024,3,4 would be a different date, regardless of whether a particular display format makes it look familiar.
A compact import note can retain four things: the untouched source string, the source's stated convention, the selected conversion locale and the resulting components. In our fictional case, those are 03/04/2024;day/month/year;English (United Kingdom);2024/4/3. This is enough to make the interpretation inspectable without pretending that the original source itself used a year-first form.
If the source convention remains unknown, record that gap instead of selecting a locale merely to make the column load. A successful conversion is a result under an interpretation. It becomes a faithful city-data reading only when that interpretation is supported by the source.
Sources & further reading
Published sources for the details and examples discussed in this story.
