Refresh Error

[Martin_Lanyon]

Hi All

I’m have issues with one of Power Bi reports refreshing due to the error message below.  Any suggestions on how to fix this error.

[Ritaf1983 · Accepted solution]

Hi @Martin_Lanyon 

Hi,

The error message indicates that there is an issue with converting varchar data to datetime, which is causing the refresh to fail. This type of error usually points to problematic data in your source. Here are some steps to troubleshoot and resolve this:

1. Review Date Columns in the Source: Check the v_PBICrematedRemains table (and any relevant tables) for any non-date values in columns expected to contain datetime data. Ensure there are no entries like "N/A," empty strings, or other non-date text.

2. Check for Out-of-Range Dates: Make sure all date values are within the valid range for SQL Server's datetime type, which starts from 1753-01-01. Dates outside this range or incorrectly formatted dates can trigger this error.

3. Clean Data at the Source: If possible, clean and standardize date values in your source database to prevent this issue from recurring.

4. Run the Query in SQL Server: Test the SQL query used in Power BI directly in SQL Server Management Studio (SSMS) or your database query tool. This will help you identify any rows causing conversion errors.

5. Add Data Transformations in Power Query: If modifying the source isn't an option, consider adding transformation steps in Power Query to filter or replace invalid date values before applying the datetime type

conversion.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Uzi2019 · Accepted solution]

Hi @Martin_Lanyon 

You must have converted your date time filed from Text to Date format which causing an error.

You can fix this by removing this step from power query. and modify your field as Date data type from actual source (SQL, Excel). 

sometimes gap and error in text filed causing this error. remove the step and do the same thing on actual Database.

I hope I answered your question!

[Ritaf1983 · Accepted solution]

Hi @Martin_Lanyon 

Hi,

The error message indicates that there is an issue with converting varchar data to datetime, which is causing the refresh to fail. This type of error usually points to problematic data in your source. Here are some steps to troubleshoot and resolve this:

1. Review Date Columns in the Source: Check the v_PBICrematedRemains table (and any relevant tables) for any non-date values in columns expected to contain datetime data. Ensure there are no entries like "N/A," empty strings, or other non-date text.

2. Check for Out-of-Range Dates: Make sure all date values are within the valid range for SQL Server's datetime type, which starts from 1753-01-01. Dates outside this range or incorrectly formatted dates can trigger this error.

3. Clean Data at the Source: If possible, clean and standardize date values in your source database to prevent this issue from recurring.

4. Run the Query in SQL Server: Test the SQL query used in Power BI directly in SQL Server Management Studio (SSMS) or your database query tool. This will help you identify any rows causing conversion errors.

5. Add Data Transformations in Power Query: If modifying the source isn't an option, consider adding transformation steps in Power Query to filter or replace invalid date values before applying the datetime type

conversion.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.