Forum Discussion
Date Conversion Error Power BI/Query
Hello Everyone,
Anyone has any idea why a date coloumn loads as text in power BI. I have tried to :
1. Split the coloumn and create the date field using the power query #date
2.I have tried to change the date coloumn > Uning locale> selected date. selected South Africa
Still no success
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
6 Replies
Hi Khomotjo ,
I don't think using locale is the best way to define datatype for a column.
Usually, locale is used to show data friendly to a certain locale the reports are being used.
In an ideal case, using #date should create a Date value. If it is not happening, better way to do this would be to change the datatype in Power Query for the column to date
Regards,
Hi,
Please share some data to work with.
if your column has the same data format. Then you can try to ceate a column from example. You just need to type in the first or first several expected output. Then it will convert the rest for you.
https://learn.microsoft.com/en-us/power-query/column-from-example?wt.mc_id=DP-MVP-5004616
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 supportHi @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 supportHi 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
