OLE DB or ODBC error: [Expression.error]

[Anonymous]

Hi everyone,

I have a PowerBI report that I built using Excel PowerQuery and loaded it all into the data model then into PowerBI. The source files and the PowerQuery file I'm using for the data model are all saved to a SharePoint online folder as is the PBIX file. Today, I refreshed the data in the Excel file (loading new tables, combining them) and loaded it to the data model but when I try to refresh the data in PowerBI, I get these errors.

TABLE 1: OLE DB or ODBC error: [Expression.error] The key didn't match any rows in the table.

TABLE 2 (this table did not change at all): OLE DB or ODBC error: [DataSource.NotFound] Excel Workbook: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. The 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider may be required to read this type of file. To download the client software, visit the following site: https://go.microsoft.com/fwlink/?LinkID=285987...

Any ideas? Table 2 is particularly confusing since its a super simple table and wasn't changed in any way.

[sblack5695]

I had that same error and it ended up being because the file was password protected, so I just saved it in a different spot and under Tools -- General Options, just erase the password. Problem solved!

[SergioAlencar]

Hi, I would like to share a solution that I found. My report was showintg the same problem but my data was being imported from EXCEL tables. I solved my problem when I realized that among several tables from XLSx file I had only one table from Excel 2013. When I converted that table to XLSx the problem was solved. I hope it can help...

[FloridaMan55051]

this was my problem. The user saved the file as '.xls' . After I updated the format to .xlsx , the error was gone.

[Mamerion]

Hi All,

I'm connecting to a data source on SQL server and faced the same "key didn't match" issue. tried all suggestions from community and almost anything from net, but none has worked for me. 

After 7 hours of research and trial and error, I discovered something related to my credential on SQL server that was causing this issue.  I'm not sure why the "db_owner" was unticked!(might be due to the patching updates on the server) I checked the box then refreshed the report and now it is working as normal. 

In SQL management Studio, navigate to the Databases\[database name]\Security\Users\ and double click on the userID to open the "Database User" window, then select the "membership" from the left side of the window.

I am the database owner so the db_owner is ticked, if you are not an owner, then make sure that at least the "db_datareader" is checked.

Hoping it helps you to save heaps of troubleshooting time.

Regards,

M

[v-juanli-msft]

Hi @Anonymous

How about installing the 64-bit version of the Access Database Engine 2010 Access Database Engine OLEDB provider on your server?

Best Regards

Maggie 

[Anonymous]

I have reported this error to the team, so we'll see if that's really what is needed but I really doubt it. 

[Aron_Moore · Accepted solution]

When you created the .PBIX did you pull from the excel stored in sharepoint or did you pull from excel stored local then uploaded that excel to sharepoint?

I suspect the latter which will give you errors without a personal gateway, system powered on and online, etc.