Forum Discussion
Date Conversion Error Power BI/Query
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 nullThis 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
Hi @Khomotjo,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support