A spreadsheet import can finish successfully while changing the meaning of a date. The value 04/05/2026, for example, can represent April 5 or May 4, depending on the convention used by the source.
Power Query gives users explicit control over data types and regional interpretation. Understanding those controls is essential when combining exports from different countries, suppliers, or business systems.
Establish what the source means
Before changing a column to Date, identify the convention used by the system that produced it. Ask for a documented format or inspect an unambiguous example.
A value such as 23/05/2026 strongly suggests a day-first convention because 23 cannot be a month. However, a file containing only dates with day values below 13 can conceal a mistaken interpretation.
Treat identifiers separately. A customer code containing digits is not necessarily a number that should participate in arithmetic. Decide the meaning of each column before selecting its type.
Apply the appropriate locale
Microsoft explains that Power Query uses locale when interpreting text and converting it into another type. Its documentation describes a “Using locale” option that lets users choose both the target type and the regional convention.
Automatic type detection for unstructured sources inspects an initial sample rather than establishing the business meaning of every value. Microsoft documents detection based on the first 200 rows, making explicit review valuable when later rows contain different patterns.
For a recurring import, make the conversion an intentional query step. A colleague should be able to see why a particular locale was selected.
Investigate errors before removing them
Power Query distinguishes step-level errors, which can prevent a query from loading, from errors affecting individual cells. Microsoft recommends examining the error reason, message, and details.
A missing source file requires a different response from a value that cannot be converted. Treating every error as a row to delete can hide the cause and silently change the dataset.
For example, suppose a monthly export contains one explanatory line among thousands of transactions. Determine whether that line should be excluded by a documented rule. Do not remove all conversion failures without checking whether valid transactions were affected.
Validate meaning as well as totals
Build a small set of reference records whose expected results are known. Include an ambiguous date, an unambiguous date, a blank field, and a value that should fail validation.
After conversion, compare those records with the source. Check the day and month independently rather than judging only the displayed date format.
Then reconcile record counts and relevant totals. These checks answer different questions: a correct total does not prove that dates were interpreted correctly, and correct sample dates do not prove that every row was retained.
Keep the original export available during review. When the next file arrives, run the same checks before relying on the report. A reliable import is a repeatable interpretation process, with exceptions that remain visible.