Switching Data Source From one Excel Workbook to another updated version causes OLE DB or ODBC error

[KevinGagel_Canf]

I created a new report and once I was happy with it I wanted to use updated information. I switched the data source to an updated Excel Workbook that contains an identical layout. Both were created from a script that gathers the data and formats the workbook. So they have all the same fields and format. Just the data in the columns is updated in the newer file.

I get this error when I apply the changes.

I do have two columns with dates in them. I have formatted the columns as Date. That didn't work, so I tried formatting the date columns to match what my BI Report is expecting (yyyy/mm/dd), that also didn't work. Same error.

Any suggestions?

[HotChilli]

You might try creating a new 'test' report from powerbi which connects to the updated Excel Workbook. You don't have to do anything except connect successfully.

--

If that works OK, compare the Advanced Editor code of the new 'test' report with the code on the original working report.  If there are differences then you can address those.

If there are no differences, copy the Advanced Editor code from the original (from the lines after the connection steps) and add it to the new 'test' report Advanced Editor.  There will be syntax problems which you will need to correct and some editing will be required but that should get a working 'new' report with the same transforms as the original.

[KevinGagel_Canf · Accepted solution]

Thanks, that gave me the insight I needed to figure out the problem.

The two files are not identical after all. The new file was generated by a newer version of the script that generates the Excel Workbook. In the old version the date columns are a string that excel converts to a date. In the new version of the script the date columns are a datetime data type.

The report is modeled off of an Excel file that was output from the old script. The new output looks identical but Power BI sees the difference as {"LastLogonDate", Int64.Type} vs {"LastLogonDate", type date}. 

Switching the query to "type date" for both date columns corrected it and now the report works. 

Thank you.