cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jburklund
Helper II
Helper II

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors