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?