Expression.Error: The key didn't match any rows in the table

[SteveS1]

I need assistance.

I get the above shown expression error.

I am in the desktop.  I  am connecting to an Azure SQL database.  It has 20 tables. I have previously direct connected to 18 of the 20.  Now i open a brand new model, and i choose to connect to previous data source.  I see all 20 tables.  I choose the 19th table.  As soon as i 'check the box' (selecting the table), the above error (The key didn't match any rows in the table) shows up in the Navigator box. 

The table has a primary key.  It also has a foreign key to another table.

This is my first post.  Have i missed providing any important information to solving this issue?

[Bres87]

Hello

I'm experiencing the same problem when I try to upload files through the ShareFolder

This is the script I have when I press the Advanced Editor:

Any Idea how can I resolve the issue ?

Regards

Bres

[lafondm]

Does it run if you just do a simple query?  I had issues such as these, while trying to tighten down the amount of data from a beginning "SELECT *" query.  I had to delete the Navigational step on original Query.

[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

[Anonymous]

There must be some special permissions required for tabular cubes (which is what is being created behind the scenes in PowerBI). I had the same issue as Mamerion, with a user which had specific schema bound permissions in SQL Server. Once I elevated to a user with db_owner permissions, it worked without a problem.

I thought the only permissions required were read permissions on the tables it was extracting data from, but it seems other permissions are required too. 

[Overhear]

The solutions mentioned here didn't work for me, but after I converted my input files from xls to csv the "Get Data from Folder" routine worked like a charm. Sorry I didn't go this route sooner! 

I used the powershell script available here to batch convert all of my input files. Edited the script to refer to xls instead of xlsx. I did have to step through the commands individually by pasting them into the ps command line before I could get them to execute, but then it zipped right through.

[Anonymous]

I encountered this error the other day and after endless searches on the internet for a solution I finally solved it on my own.  The issue was so unexpected that I wanted to share it in case it helps anyone else. 

I noticed that in the Power Query Editor, on the step prior to when the data gets populated into the spreadsheet, it was displaying "Table" on some files but "Error" on other files.  All of my source files were .xls files, and they all contain roughly the same amount of data, but then I noticed that some files were more than twice as large as the other files. And the files that were twice as large as the others were the files that said "Error". 

So I opened one of the files that said "Table" and one of the files that said "Error".  The both looked the same, but the file that said "Table" gave me this warning: "The file format and extension of [file name] don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?"

Turns out that the .xls files were actually .xlsx files that had been saved with an .xls extension! Which was fine since I built my Power Query on those files, but what had happened was that the end user was opening some of the files to view them and then resaving them.  And since the files had an .xls extension (even though they were really .xlsx files), when Excel saved the file it reverted to .xls format, which threw away some of the formatting that is inherent to the .xlsx format.  This also had the side effect of increasing the file size (since apparently the .xlsx format is more efficient memory-wise than the .xls format), which is how I first noticed a difference between the files. 

The solution was to re-open the offending files, save them in the .xslx format, and then rename them to change the file extension back to .xls (since that is what my Power Query was filtering on).  Problem solved!

[frankmtf]

Thanks ! You've saved my life ! 😄 

[Anonymous]

I fixed this my version of this error message by changing the name of the source worksheets back to their original name "Sheet1" after a collegue had renamed them when adding new files to the folder. The other check is to ensure the headings match perfectly.

Hope this helps someone, it was very frustrating!

[altomost]

you can make this error occur if you e.g. :

1. connect to a database
2. select any table and load it
3. click home - edit queries
4. click source under applied steps
5. click advanced options in the popup
6. type SQL statement e.g. select * from [yourtablename]
7. click ok
8. click close and apply
9. You now see the error above (Expression.Error: The key didn't match any rows in the table)

Solution

1. click home - edit queries
2. under applied steps, remove navigation
3. click close and apply
4. the error is fixed

[OB]

Thank you so much for you post! My issue was a little different - I was working with the SharePoint on premise list, which looked fine in PBI Desktop, but wouldn't refresh in the Power BI Service giving me a mashup error.  After many different attempts to fix it, I created a duplicate SharePoint list to test and that's when the Power BI Desktop gave me "the key didn't match any rows in the table" error and your post just made my day!  Your solution worked 100%!  This is great 🙂

[DevinHarrington]

Bumping thread as I am running into a variation of this scenario.

-Have the latest PowerBI desktop version.

-.pbix file does various ODBC connections / SQL Server connections / Google Analytics.

- I can refresh the data with no issue by opening the .pbix file and selecting "Refresh" from the home screen (first screen when you open the .pbix.) 

- Each time the data-set tries to refresh from powerbi.com, I get the "Expression.Error: The key didn't match any rows in the table" error.

Anyone have any idea what could be causing this?  I don't see any warning on any of my queries in edit query mode.

Appreciate any input that can be given!

Thanks

Devin

[kcantor]

I have had this happen in a group. To solve it (within a group) I dropped another copy of my pbix file into the group folder and replaced the older file. Then, when I hit refresh, it worked. I did not lose any work.

Are you encountering this in a group or in your own workspace?

[DevinHarrington]

I am not sure how to tell if it is group / own workspace.  

Our repository has a number people adding reports to it, but all are using the personal gateway. 

I tried deleting the file from online and republished but still get the same error.   

[kcantor]

I did actually have this happen one other time. There were changes made to the table I pulled my information from and it threw the same error. I 'fixed' it by going into the query editor and stepping through each step again. Once I completed that, it refreshed in the powerbi.com app. I do not know why it didn't refresh before but just pulling up each step of the query and moving to the next made it work.

By the way, if you pull out the leftside menu where your dashboards and reports 'live' if will say "My Workspace" at the top or will have the name of a group.

Hope this helps.

[DevinHarrington]

After latest update, I deleted the file from online and republished.

Now sometimes the file refreshes with no error and other times still gets the "The key didn't match any rows in the table".  Not sure what to do here besides wait for next update. 

Thanks for input!

Devin

[DevinHarrington]

I am still getting this error after the latest PowerBI update / clean push to web.

Have stepped thru all the .xlsx and solutions I've found online w/ no luck.

Does anyone know if PowerBI has a logging component so I could try to find the step this error occurs for me?  Hard to track down since the error doesn't occur on my desktop ever, only scheduled refresh from personal gateway.

[AndrewDang]

I have this issue when I try to load 43 similar excel files from one folder into Power BI. I am wondering if there is any tips to help with this?

THanks;

Andrew

[pqian · Accepted solution]

You are seeing locally cached results been returned. I think the 19th table is new in the database and therefore doesn't exist in the local cache. In this case simply refresh from the navigator (button right on top of the error) should work.

In the next release of desktop we plan to automatically refresh your local cache if we see this error.

[HoustonMike]

I received this error as well, but the refresh didn't clear the message. However, when I deleted the 'Navigation' step and then refreshed, the error successfully cleared. In your post here, you mentioned a change in a subsequent release to auto refresh the local cache. Was that implemented?

[SteveS1]

That was easy!  Thank you!  I am just beginning my PowerBI steps.  Now, i am off again into the journey.