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

[tayloramy]

I have a Power BI report connecting to SQL Server, and am getting some really odd behaviour. 

WHen first developing, everything worked fine. I am pulling a table from an on prem SQL Server instance and everything is good. 

THen suddenly I started getting this error when trying to refresh: 

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

If I make a new query as a SELECT * FROM statement, that loads no problem, but when I navigate to the table without using a query I get this error.

I also noticed that if I delete all columns but one from the table, refresh, then remove that delete columns step in power query, it will refresh no problem for a few more days before the problem returns. 

Any ideas? 

the power query is as simple as it can get: 

Thanks

[Nabha-Ahmed]

Hi @tayloramy 

I have seen this behavior before when connecting Power BI/Fabric to an on-prem SQL Server table. The error:

“OLE DB or ODBC error: Type mismatch (0x80020005 DISP_E_TYPEMISMATCH)”

almost always indicates a mismatch between the SQL Server schema and the schema cached inside Power Query.

Below are the most common root causes and recommended fixes:

---

1. A SQL Server column’s data type changed

Power BI sometimes keeps the old metadata and fails when the actual SQL type changes (e.g., INT → VARCHAR, DATETIME → NVARCHAR, etc.).

📌Fix:
In Power Query → Refresh Preview
Then remove or re-apply the Changed Type step.

📘Microsoft Docs – Data types & schema issues:
https://learn.microsoft.com/power-query/data-types

---

2. New columns added or column order changed

If the table schema changed after the first load, Power BI may mismatch the cached structure with the current one.

📌Fix:
Power Query → Home → Clear Cache
and
File → Options → Data Load → Clear Cache

📘Documentation on schema drift:
https://learn.microsoft.com/power-query/schema-drift

---

3. A single “corrupted” value in SQL Server

Sometimes the entire refresh fails because one row contains invalid data for the column type.

📌Fix:
Validate columns using SQL (example for date columns):

SELECT *
FROM YourTable
WHERE ISDATE(DateColumn) = 0;

---

4. Power BI cached metadata

Your observation that removing columns then undoing makes it work for days strongly suggests a metadata caching issue.

📌Fix:
Data Source Settings → Clear Permissions
Reconnect to the data source.

---

5. Using a SQL query bypasses metadata issues

Since SELECT * FROM works, the table navigator is likely the source of the mismatch.

📌Workaround:
Use an explicit SQL query:

SELECT col1, col2, col3
FROM dbo.YourTable;

📘Microsoft guidance on using SQL statements:
https://learn.microsoft.com/power-bi/connect-data/desktop-directquery-about#use-sql-statements

[Selva-Salimi]

Hi @tayloramy ,

This issue happen when an NVARCHAR column contained some non-numeric characters, but Power BI had auto-typed that column as a number. When new data with incompatible values appeared, the refresh started failing.
The fix is to explicitly set that column’s type to Text in Power Query instead of using the auto-detected numeric type.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution ✔️ to help the other members find it more quickly.

[tayloramy]

Hi @Selva-Salimi, 

Power BI automatically determined the column type for what I think is the culprit column to Text. The error seems to go away (at least for a little while) once I explicitly cast it as a whole number, though so far it has still come back after a few days with no change to the data. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[Anand24]

Hi @tayloramy,
This error is usually when either some field's datatype has changed in the backend table or if you are performing at type conversions in Power Query Editor. I do see you aren't doing any type conversions though from the M-query snap you provided. Might just be a temporary bug. Also, is it because of date/datetime column?

Is the issue still persisting when you try to refresh normally (without sql statement or remove/undo remove steps)?

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[tayloramy]

Hi @Anand24, 

The strange thing here is the table hasn't changed, we're still in development of this reporting so the table hasn't even gotten any new data, same structure and same data. 

Yes the issue persists when refreshing normally. From my troubleshooting, it appears to be because of a string column that contains numbers, though this column is properly a string on the database because it can contain string characters, it just happens to not right this moment. 

Any ideas? 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.