Parsing Mixed Date Formats (MM/dd/yyyy vs dd/MM/yyyy) in Power Query

Quick Answer

Using Replace Values to standardize the date strings before changing the column's data type is the answer because Power Query's date parser needs a single, unambiguous format to interpret correctly, and a column mixing MM/dd/yyyy and dd/MM/yyyy rows has no such consistency on its own. Many day-month combinations are genuinely ambiguous without knowing which convention produced them, so trying to convert the column's type directly, before resolving that ambiguity, risks silently misinterpreting some rows by swapping their day and month values rather than throwing an obvious error. Replace Values lets you fix the ambiguity while the data is still plain text: you can standardize delimiters, reorder the day and month segments, or otherwise normalize every row into one consistent pattern. Once the entire column follows a single format, changing the type to Date using a matching culture setting, such as en-US for MM/dd/yyyy, parses every row correctly and consistently rather than only the rows that happened to already match. The broader lesson is that Power Query's type conversion functions assume a single format contract for the whole column; whenever a scenario describes mixed or ambiguous text formats feeding into a type change, the fix is almost always to normalize the text first with transformations like Replace Values and only convert the type once the format is uniform.