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

[Anonymous]

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:

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:

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

[DnyaneshwarM]

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]

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]

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]

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. 

[martaTr]

why power bi cannot indicate where is a problem? i have 40 columns 😞 how can i check them one by one without loosing hours on that , that's crazy

[basel777]

Agreed. I usually go into each datasource and make the primary and foreign keys all the same datatype, and that helps. PowerBI evidently remembers what the datatypes are for the rows of data that are appended, and sometimes, changing the datatype after you append doesn't change all the datatypes.

[josepchile]

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

[José]

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]

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]

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]

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]

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 · Accepted solution]

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

[basel777]

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]

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 

[Anonymous]

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.