cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patlaff Frequent Visitor
Frequent Visitor

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

Not sure if this will help everyone, but I received this error because I had not yet given permissions to the SQL account for the new database I was trying to connect to. Make sure the account you're using has at least read permissions, otherwise this error will be thrown.

great_AS Member
Member

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

Hello @tonysellars,

 

I had same issue and with your trick I manged to fix it.

 

However, in the next steps of the query i got the following message:

 

2017-02-03 15_25_53-Monthly Report TEST_V2 - Query Editor.png

 

Know how to fix this?

 

Thx in advance.

 

Cheers,

Andrea

tonysellars Frequent Visitor
Frequent Visitor

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

The query itself contains a lot of metadata related to the source you are pulling from and it seems that this can get a bit out of whack.  The case you are looking at here you have a many different steps you are going through.  The issues for this error have typically been in the source step.  To see all the steps and see how the query progresses you can take a look at the progression as each step generates a result which is operated agaist by the next step.  The original trick to refresh the query, has worked for me and I was glad to see that it seemed to work with Power Query as well as it uses basically the same mechanism.  If you do continue to get an error even after refresh, take a look at the advanced editor and see if something jumps out as not being in sync with your source in one of the later steps.

 

AdvancedEditor.png

 

Hope this helps.

Novus Visitor
Visitor

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

Remove Navigation step, as it attempts to read full table from DB and that does not match with your source querry

DandyDan Frequent Visitor
Frequent Visitor

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

My fix was slightly different.  I needed to update the db tables and my credentials, but this solution got me 90% there.  

mustafay Frequent Visitor
Frequent Visitor

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

Hi, 

I got same error, in my case excel sheet names were different. After changing to same name the problem solved. 

mustafay Frequent Visitor
Frequent Visitor

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

Hi, 

I got same error, in my case excel sheet names were different. After changing to same name the problem solved. 

 

Mustafa Yalcin

Jmenas Member
Member

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

Hi All,

 

It might be resolved but if anyone had this problem with googla Anyltics API.

 

I found that the solution is to add a custom column and transform the query into a normal table. Otherwise power bi service, in my case, will no identify the correct table. 

 

 

Best,

J.

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

I am using power bi that has a dataset of multiple excel spreadsheets (tables) on sharepoint. I had no problems with refresh until....I had to add an additional table to the power query.  I have two issues here...(1. how to fix the refresh 2. how to prevent this from happening as the dataset changes). 

 

 

1. I get this error and I have followed the steps to resolve.  When I use the desktop app, I hit refresh and everything works fine. It updates without error. When I go to "edit query" to see if there are errors, there are no errors.  All I am going off of is the email I get that says the refresh failed.  How can I narrow it down if there aren't errors?  Is this error occurring for a specific table that I can isolate?

 

2. What caused this=>  I added a table and created a power query in excel.  When I appended the query to my existing query, what it does is creates a new tab with it appended.  The only problem is that I have the dataset source pointing to the table "master" and now I have a table that is called "appended1".  I have other tabs pointing to a tab called master and now I need to change the reference to appended1.  It seems like I did something wrong with power query, so I went back through that process and tried other options when appending and still got the same result. I see myself having to fix this everytime a new tab is created. How can be more effective with this process?

Highlighted
jetboy2k Regular Visitor
Regular Visitor

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

Thanks! This fixed the problem for me!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Top Kudoed Authors
Users Online
Currently online: 6 members 2,770 guests
Please welcome our newest community members: