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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

The key didn't match any rows in the table

Hi community,

 

I have a very strange error "The key didn't match any rows in the table" when refreshing a dataset in the PowerBI webservice. The dataset refresh works fine when I do it in PowerBI Desktop.

 

I have seen similar questions where the solution was that an excel worksheet in the source was renamed. However, my only sources are dataflows and they do all work fine as well. 

 

Any ideas where I could look for an error?

 

Thanks

Martin

1 ACCEPTED SOLUTION
Anonymous
Not applicable

This is what I received from MS and it solved the problem for me

 

As informed earlier we have reached out to our Product team and got an update form them stating that the fix for the issue you are facing would be in next 2 -3 weeks. Workaround ========================================

The workaround is to turn off the SameSite settings in the browser. Go to chrome://flags or edge://flags and search for SameSite, disable the 2 (edge) or 3 (chrome) features that show up. Restart and it will work. Further we request you to try the above workaround and please let us know if you are still facing any issues. We apologies for the inconvenience caused. I look forward to hearing from you soon!

View solution in original post

39 REPLIES 39
JamesBockett
Helper I
Helper I

Hi All

 

I note that this thread is from a couple of years a go but, I have recently started experiencing the problem too. There are 2 datasets that come from the same dataflow. Both datasets and the dataflow used to auto-refresh but now, one of the datasets has stopped and give the error message described in the original post.

 

Any ideas on what to do?

Now resolved on my end. Seems like there's a number of scenarios which can cause this issue but, in my case, it was down to the user's attempting to refresh the dashboard not having appropriate admin permissions in relation to the  dataflow we're sourcing data from.

PaulBoden
Frequent Visitor

I'm specifically getting this issue when a colleague within the organisation tries to refresh the data. 

 

I can still refresh the data (via gateway and dataflow) but he cannot. He's signed me out and is using his own (valid) credentials in the source settings. We're both using Power BI Desktop...

 

Stumped. Any suggestions welcome. 

Mseeram
Frequent Visitor

Hi @Anonymous @Alex_Rajkov @Anonymous 
I am having the same issue since 4 days. How do I resolve this issue?

 

MichaelJ64
Frequent Visitor

Hi community,

 

Newbie here.  I've checked the thread and I think I am getting the error for an entirely different reason, probably related to my limited skill in M.   Removing my (system generated) Navigation step didn't work - it removed the label for the mail directory 🙂 .  I am not working with streams, nor am I working with database software or Excel.  I have certainly tried the several approaches to refreshing my source data with no success.

 

My process is to import all the e-mails in a subfolder and then process each one.  The body of each e-mail is text within which are eight parameters I want that I find be text manipulation after creating a table with each text line being a row.  This all works fine for the single case.  As soon as I try to process all the e-mails my code fails.  The text processing is done in a function I wrote called "ExtractSalesForce".  (This is an e-mail, I don't want to fetch from SalesForce directly.)

 

The screenshot of the error message shows the top of the text in the body of the e-mail.  The code fragment shows successful use of my function on a single e-mail, and the statement causing a problem.

 

The e-mails end up in the table "RemovedDuplicates".  It has one column named "Body.TextBody".  The 'Text.Replace' is to remove commas so they don't create a new record.  I change them to a hyphen so I can text match to find a parameter.  As commented, QueryTable2 has the desired information.  I then want to add a column to "RemovedDuplicates" that has a record in it generated by my function "ExtractSalesForce" (the eight parameters I want).  This is then expanded into the eight parameters (one per column) and the originating column deleted.

 

Here is the error text:

 

image001 (1).jpg

 

 

 

 

 

 

 

 

 

 

And the code fragment is:

 

    QueryTable2 = Table.FromRecords(ExtractSalesForce(List.Transform(Record.ToList(RemovedDuplicates{1}),

                        each Text.Replace(_,","," -"))  )  ),

/* this code works - QueryTable2 has the eight parameters of the second e-mail

*/

    LoopOverQueries = Table.AddColumn(RemovedDuplicates,"NewRowQueryElements", each

                         ExtractSalesForce(List.Transform(Record.ToList([Body.TextBody]),

                        each Text.Replace([Body.TextBody],","," -"))  ) ),

 

I would greatly appreciate any suggestions to get this to work, including better code.

 

Michael J.

Hi Community,
My issue was resolved by the system updates.  Many thanks.
Michael J.

Anonymous
Not applicable

Hi everyone! i'm experiencing the same error but i just refresh the dataset. No dataflows or gateway.Pls help!is there any solution to this?

apods1979
Helper I
Helper I

I have the same problem, that is, when I refresh the dataset that is connected to the dataflow, I have the same error.
However:
My case is a little different, because I have two different tables in two different dataflows ...
I change the datafow and the entity via Parameters ...

Dataflows are successfully refreh
Dataset is refreshed with error.

I did a test using the same dataflow for the two different tables and then I was successful !!!😎
But I can't believe I can't use two different datafows via parameters ...🙄

Anonymous
Not applicable

I am trying to gather information on how others have fixed their refresh issues. Were your dataset refreshes suddenly resolved or did you apply the datetime conversion from text method?

 

I have a couple datasets which are still failing to refresh due to the "keys do not match" error. After contacting Microsoft, it seemed to get fixed over the weekend? However, the dataset failed to refresh again this week.

 

Any input is appreciated. I hope to find the root cause soon because this is bringing into question the reliability of dataflows for self-service.

Same to the above, the dataset just started working on its own again. No changes on my end!

 

Any updates from Microsoft?

Anonymous
Not applicable

Yes, I just finishing chatting with the support team. It seems this Keys match dataset refresh error is a problem that exists in multiple clusters which is why some may be fixed and others not. The error originally was pushed to their production Service servers and they are in the process of resolving it across all clusters.

 

My datasets are refreshing again. I had to perform the navigation steps again and that resolved it on my end.

 

Microsoft states they will release the root cause in the future.

 

 

Anonymous
Not applicable

This is what I received from MS and it solved the problem for me

 

As informed earlier we have reached out to our Product team and got an update form them stating that the fix for the issue you are facing would be in next 2 -3 weeks. Workaround ========================================

The workaround is to turn off the SameSite settings in the browser. Go to chrome://flags or edge://flags and search for SameSite, disable the 2 (edge) or 3 (chrome) features that show up. Restart and it will work. Further we request you to try the above workaround and please let us know if you are still facing any issues. We apologies for the inconvenience caused. I look forward to hearing from you soon!

SameSite is client-side and browser-specific. How would this at all affect a server-side data connection issue from PowerBI?

Anonymous
Not applicable

Hi, I did not change anything on my end. Just started working again

TheBeerBaron
Regular Visitor

We have been seeing this since Monday at noon EST as well as are seeing in a Flow in Power Automate that connects using the SQL V2 connector through the gateway. Last night we upgraded our gateway cluster and were seeing the same error. This morning in the cloud  the error changed:

Something went wrong
Cancellation occurred when sending or receiving a request.
Please try again later or contact support. If you contact support, please provide these details.
Cluster URI: WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID: 77125b6b-cbef-4f8c-b3a9-0b6f12634c02
Request ID: 71f294a6-fa31-8fdb-8120-a10b09c5e25c
Time: 2020-02-14 12:30:10Z
 
however the error in the gateway logs on the server is the same. 
 
The model refreshes fine in Desktop and using a personal gateway.  

My dataset in my Premium Workspace is again working this morning. Microsoft must have fixed it mostly everyone now. My guess is there was some bug in how a dataset grabs information from dataflow? For some context, our Dataflow is doing a pull from a REST API source - no Gateways are involved. Set to update every 30 minutes.

 

Unrelated, but are any of you doing automated incremental updates with your data? If so, is this working well for your implementation?

Anonymous
Not applicable

It looks like, at least for us, the issue was fixed overnight. I have refreshed my report this morning successfully. Hopefully, everyone else has a similar result.

Anonymous
Not applicable

The official explanation for my issues is "the dataflow was missing the date, date/time type in the deployment".  Hopefully yours all start working but if you are still having issues they recommend you can either try:

  • downloading the newest Data Gateway version

or a workaround

  • in the Dataflow change the data, date/time type fields to text, then in Power BI Desktop change the field type back to date, date/time type

 

 

All is now working fine for me with no workarounds around date fields etc being required, so the issue appears to have been resolved. 

I'm not using Data Gateway at all here, as my Dataflow is doing a REST API call externally to bring in the data.

 

Changing the date fields to be a Text type in online Power Query is an interesting fix, but I don't think is feasible long term, as then you cannot do any incremental update automation activation on your dataflow... if I remember correctly. Luckily, I am not currently requiring those, so I can try the workaround they gave you.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors