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
Anonymous
Not applicable

OLE DB or ODBC error: Type mismatch. (Exception from HRESULT: 0x80020005)

Hi,

 

I'm really struggling to understand the error I'm having and I really hope someone can help me.

 

In Desktop I'm using an ODBC Connection to access a number of different data sources. One of them keeps throwing up the following error:

 

01.PNG

In the ODBC connection I'm using SQL to bring back the data. I have a number of different date fields in this query.  When I have the main date at, for example '2019-02-04' the report works fine. But when I push this back to the start of 2019 it throws up this error. I'm not making any changes to the format of any data fields after it comes in to Desktop:

 

02.PNG

 

The only think I'm doing it adding a couple of custom columns and doing a bit of a clean up.

 

I've read other posts about this but none of them make sense to me. Can anyone help?

 

Thanks,

MarkJames

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @rocky09 ,

 

I had a similar report set up on the Power BI Service that failed to refresh but provided me with a more detailed error. It turns out one of the fields contains string values when the ODBC connection was expecting date. When I checked the set up for the original data source the field in question was shown as a string so I'm not sure where the change occured.

 

Luckily, in this instance, I think I'll be able to omit the field from the report as I don't think it's entirely necessary. Hopefully that will negate the issue.


Thanks,

MarkJames

View solution in original post

14 REPLIES 14
DnyaneshwarM
New Member

Upon reviewing the data, I found a mix of data types including both text and numeric values. After correcting the data types accordingly, the process proceeded without any issues.

sjs210
Advocate I
Advocate I

One thing to try:
Create a duplicate source of the problematic query, and load it into a new table (TEST) in the power bi dataset.  Use Dax Studio (External Tools) to identify the column type by running a query on DMV>TMSColumns.  Send output to Excel, filter for the new TEST table and original table, pivot on the table name, with the column name and explicit column type.  Made it very easy to find the mismatched column type!

Aberg8
New Member

I recieved this error because a field type in a SQL table was changed.  PowerBI was consuming a view that contained that field, and the view retained the incorrect field type.  I scripted the view to alter it, didn't make any changes, and run the "altered" unaltered sql.  The new version of the view had the correct datatype and PowerBI digested it with no issues..

JHAN
New Member

Hi All

 

I experienced the same error in the Desktop version, when changing the sources (excel file). The error occured due to a change in the excel file due to an update of the software that I extracted the data from. 

Error message: OLE DB or ODBC error: Type mismatch. (Exception from HRESULT: 0x80020005)

 

The issue arised due to incorrect type of some date columns.

To fix the issue, go into Power Query Editor and right click on each column. Go to: Change Type and check what type each column is in your table.

In my case the issue arised because I had some date columns that had to be changed to the type Date. When the date columns were updated the error disappeared. 

 

This also worked for me. I had several date and number columns in string format. I changed the format and worked. Thanks!

José
Regular Visitor

Great! It worked out for me, but instead of date was with a numbers column, was appearing as string, changed it to whole number and worked.

Anonymous
Not applicable

Bom dia!

 

Meu erro foi muito parecido com este! Fui até a base de edições do Power Query dentro do Power BI e, por mais que as minhas colunas já estivessem todas formatadas, assim como uma em específico estava como data, retirei a alteração de Tipo da coluna e re-fiz este processo. Além disto, na minha base (planilha em Excel) a formatação da coluna de data estava como texto e aconteceu algum problema ai entre a conexão da base e o PBI.

 

Acredito que possa ser isto, mas confira as colunas de data principalmente ou refaça todo o processo de alteração do tipo da coluna e espero que ajude!

 

Abraço

Anonymous
Not applicable

I received this issue on a report that was fine for over a year as well, so I did what everyone said to do...

 

I went back all the way through a ton of steps that I had previous done, and worked, and coppied them over to one note and deleted them one by one off of PBI Query Editor. The only item left was the Source (only then was I able to refresh and get new data).  Some of these steps included these items pasted below, but the only issue that popped up when I started adding back in the steps was in the word position…  It was previously singular, and now only worked if it's typed in plural form… (Split Column by Positions)

 

Split Column by Positions
= Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 7}, false), {"Column1.1", "Column1.2"})

Change Type
= Table.TransformColumnTypes(#"Split Column by Positions",{{"Column1.1", type text}, {"Column1.2", type text}})

Filtered Rows

= Table.SelectRows(#"Changed Type6", each ([Column1.1] = "  8049 " or [Column1.1] = " 19100 " or [Column1.1] = "806001 "))

Renamed Columns

= Table.RenameColumns(#"Filtered Rows",{{"Column1.1", "Job"}})

Trimmed Text

= Table.TransformColumns(#"Renamed Columns", {{"Job", Text.Trim, type text}})

 

Anyways, I hope this helps someone else in the futue.  PBI Novice.

IG

jjarkowsky
New Member

Hi, 

 

I am receiving the same error with my data and cannot figure out how to fix it. 

Would it be possible to send me file to someone who can assist? I have been trying for hours. 

 

Thank you!

rocky09
Solution Sage
Solution Sage

check the formats of the Column.  First remove every step except source and try one by one. You will find which step is throwing the error.

 

Anonymous
Not applicable

Hi @rocky09 ,

 

I had a similar report set up on the Power BI Service that failed to refresh but provided me with a more detailed error. It turns out one of the fields contains string values when the ODBC connection was expecting date. When I checked the set up for the original data source the field in question was shown as a string so I'm not sure where the change occured.

 

Luckily, in this instance, I think I'll be able to omit the field from the report as I don't think it's entirely necessary. Hopefully that will negate the issue.


Thanks,

MarkJames

So, what worked for me was deleting the steps in the problem query, 1-by-1, and then Saving/Applying the changes. I kept working backwards until the error stopped popping up, and then I was able to diagnose what was causing the issues. 

Anonymous
Not applicable

Hello, I understand the source of the problem.
After checking the column types in Power Query, you will need to make sure in Power Bi Data that you have defined the columns in the same format as those of Power Query. The problem arises when the Power Query column type is equal to the column type in Power BI Data.

 

By example 

 

Vic150_0-1661966228720.png

Vic150_1-1661966280115.png

 

Anonymous
Not applicable

I am sure this is to do with the latest power bi updates. I have models that I created in 2019 that are giving me this error and I have noticed that the data types you set originally seem to revert to text. 

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.