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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cjklatt
Frequent Visitor

ISOLATING ERROR: OLE DB or ODBC error: type mismatch (exception from HRESULT: 0x80020005

Is there an efficient method to isolate an offending column or value mismatch?

OLE DB or ODBC error: type mismatch (exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

 

Spent better part of 2 days trying to load a new Excel source into an existing appended table with 100+ other excel sources and 1k columns. I knew what I was looking as far as a value that was out of spec but eventually and very inefficiently removed/split the data and reloaded until I got to the offending column which had a Text value in a Number column.

 

I updated the Excel file to remove the Text value and new file loaded with the Appended table no further errors.

 

Does anyone have any tricks to short cut this type of isolation when working with large combined datasets?

 

Many thanks in advance!

2 REPLIES 2
DataInsights
Super User
Super User

@cjklatt,

 

One approach is to set the column's data type as Text to allow both numbers and text to be imported. Then, create a custom column in Power Query that checks whether the value is a number:

 

Value.Is(Value.FromText([ColumnOfMixedValues]), type number)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks - had thought about that but have over 1k columns so would be a lot to run this for every column. For the most part the data I am collecting from PM trackers is clean. But every so often in collection a numbers and texts and dates get mixed and could be in any of the columns.

In Query -the initial acquistion always works, the append doesnt error. Once the load runs the error hits at the end but cant tell where its at, just seems like there would be an easier way to isolate.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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