cancel
Showing results for 
Search instead for 
Did you mean: 
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
Aron_Moore
Solution Specialist
Solution Specialist

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

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

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. 

Aron_Moore
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.