Hi @Khomotjo,
The Using Locale option in Power Query works correctly only when every row in the column follows the exact same date structure. If the data format is inconsistent, the conversion may fail.
The #date() function is very strict. If even a single row contains a blank value, non-numeric year/month/day, extra spaces, placeholder text like N/A or -, invalid dates such as 00/00/0000, or swapped day and month values, Power Query may either keep the column as Text or generate hidden errors. One problematic row is enough to prevent proper conversion.
When creating a date from separate Year, Month, and Day columns, it’s safer to wrap the logic inside a try … otherwise null statement: try
#date(
Number.From([Year]),
Number.From([Month]),
Number.From([Day])
) otherwise null
This ensures valid rows are converted while invalid rows return null instead of breaking the entire query.
If you’re converting a full text date column, the most reliable method is to use Date.FromText() with a locale and wrap it in try ... otherwise null: try
Date.FromText([YourDateColumn], "en-ZA") otherwise null
This approach converts correctly formatted dates, ignores problematic rows, and prevents the whole column from failing. In short, whenever your data may contain inconsistencies, combining try … otherwise null with Date.FromText() is the safest and most stable solution.
Thanks,
Prashanth Are
