Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jburklund
Helper III
Helper III

Debugging Date Conversion Error

I am getting the following error when trying to load data from an Azure SQL database in both Excel and PBI Desktop: "OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Conversion failed when converting date and/or time from character string.."

The database has 10 date columns and millions of rows. Is there a way to trace which column is having the conversion error? Better yet, which column and which row numbers? I'm guessing there are special characters in one of the columns that is causing the error but I would like to know which column. 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @jburklund ,

 

Whereabouts in your step list does this occur? Is there a specific step that triggers the error or is it immediately at Source/Navigation?

 

If it's after a certain step, then you may be able to select the step before it, go to the Home tab > Keep Rows > Keep Errors. You could also try inserting steps before the error steps that perform Clean and Trim functions on all of your columns from the Transform tab > Format > Trim/Clean.

 

If it's immediately on load, then I'd recommend running a query against your server, something like this:

//Column-by-column
SELECT Col1
FROM yourTable
WHERE Col1 like '%[^a-Z0-9]%'

 

Re-run the query for each column in turn until you hit one (or more) that contains special characters.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

Hi @jburklund ,

 

Whereabouts in your step list does this occur? Is there a specific step that triggers the error or is it immediately at Source/Navigation?

 

If it's after a certain step, then you may be able to select the step before it, go to the Home tab > Keep Rows > Keep Errors. You could also try inserting steps before the error steps that perform Clean and Trim functions on all of your columns from the Transform tab > Format > Trim/Clean.

 

If it's immediately on load, then I'd recommend running a query against your server, something like this:

//Column-by-column
SELECT Col1
FROM yourTable
WHERE Col1 like '%[^a-Z0-9]%'

 

Re-run the query for each column in turn until you hit one (or more) that contains special characters.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Hi Pete - it is immediately at load. Can I do this SQL statement within PowerQuery? My SVD does not currently have any software for SQL queries I believe. 

@jburklund ,

 

Yes, you can create a new blank query, select SQL Server as source type then, when the database dialog comes up, enter you credentials and put your SQL query here:

BA_Pete_0-1636385430531.png

 

Once you've run the query for one column, you can just hit the little cog next to the source step in Power Query. This will open this query window back up so you can just adjust the query for your new column(s).

 

Pete

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thank you! This should help me find the errors. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors