Refresh Fails For Unknown Reason - OLE DB or ODBC error: [DataFormat.Error] Unspecified error
Data Model Summary:
2 Access DB with Historical Data & YTD Data that get merged into a “Combined Sales Data” Query.
1 DB is historical and doesnt change, 1 DB is a monthly append of the prior month sales volume.
Total Cardinality is roughly 20 Million Rows. So the refresh takes some good amount of time, and troubleshooting is very lengthy. Also have some accompanying lookup tables that have no refresh issues.
Each time I append data into the “Current” Access DB and go into Power BI Desktop to Refresh I get the aforementioned Error. “OLE DB or ODBC error: [DataFormat.Error] Unspecified error”
Similar to the problem described in the link below and the one suggestion regarding the Access 32 Bit VS 64 Bit doesnt work.
The report seems to refresh in the Query Editor, but in the actual report the refresh fails “OLE DB or ODBC error: [DataFormat.Error] Unspecified error”
Sadly, I also don’t get a copy of what data rows are causing the error, it just cancels the refresh all together. I have tried enabling the keep errors function to no avail.
What tools / tips / tricks are available for me? Ive tried finding the culprit in Dax Studio with no luck.
Any suggestions? Any way to identify the rows that might be causing the issue?
I have done this, both in Power BI & Source DB. They are all set accordingly. The refresh seems to fail whenever it arrives somewhere in the “new” portion of the DB that was appended.
try troubleshoot with the following since you are appending tables:
Does it work with only source 1?
Does it work with only source 2?
If it’s success with both source 1 and 2 most probably are the append. The usual suspect is number fields and date fields, especially when the data source is from different country as the number format and date format could be different.
For number, some use ‘,’ separator for thousands and some use “.” for separator.
So try to make dates and numbers into text data first and then reformat it. This reformatting to text must happen at 2nd step after the source step.
Hi @Despo, we’ve noticed that no response has been received from you since the 15th of August. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
