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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.