Forum Discussion

kcantor's avatar
kcantor
Icon for Community Champion rankCommunity Champion
10 years ago
Solved

Expression.Error: The key did not match any rows in the table

I have a data set that has refreshed successfully in the past. It was built 3 days ago and refreshed fine for two days. Now I cannot refresh it either on the desktop version or from services. The err...
Need Help
  • tonysellars's avatar
    tonysellars
    10 years ago

    Ran into this issue myself within the Power BI Desktop and was able to resolve the issue through the following:

     

    1. Select Edit Queries from the menu to open the Query Editor
    2. Use the left hand menu to select one of the queries having the issue (will have the triangular warning sign)
    3. On the applied steps menu at the right select the source step.
    4. In the top menu bar, select Refresh Preview.

    In my case there were several queries having this same issues and the steps above resolved each of them.

     

    Hope this helps -

Mamerion's avatar
Mamerion
Frequent Visitor
8 years ago

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

  • ThomVF's avatar
    ThomVF
    Icon for Advocate IV rankAdvocate IV
    7 years ago

    I just wanted to thank you for your post.  It helped me solve my issue.

     

    I have my gateway configured to use a specific user on my SQL Server database.  This user did not have db_owner set.  Once I set that, my refreshes started working fine.  I was thrown off bc the refreshes were working from Power BI Desktop but not in the Service.

     

    Of course, these errors are so cryptic, you could spend a whole week trying to figure out what the heck they mean.

     

    Anyhow, you saved me a lot of time and I thank you.

     

     

    • Mamerion's avatar
      Mamerion
      Frequent Visitor
      7 years ago

      Good to hear that it helped you to resolve the issue :)

  • pooyan's avatar
    pooyan
    Regular Visitor
    2 years ago

    Thanks a lot! it did help me