Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

OLE DB or ODBC error: [Expression.error]

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.

1 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.

View solution in original post

7 REPLIES 7
sblack5695
Frequent Visitor

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
New Member

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...

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

Mamerion
Frequent Visitor

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. 

 

PowerBI The Key did not match Solution.JPG

 

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
Community Support
Community Support

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
Not applicable

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

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.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors