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

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
ms_powerbi
Frequent Visitor

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

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

angelicatactay
Regular Visitor

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 ...🙄

SBeat
Helper I
Helper I

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?

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.

 

 

ms_powerbi
Frequent Visitor

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

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

ms_powerbi
Frequent Visitor

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?

Mark_Tracy
Frequent Visitor

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.

I just started having this issue. Does anyone have a solution?

Anonymous
Not applicable

So I was on the phone off and on with PBI Support for several hours last night and they had made great progress resolving the issue. I know they were successfully able to debug several of the issues my datasets were having but I was still having issues with few. Ultimately I just manually triggered refreshes on the ones I having issues with and they refreshed successfully so it may be resolved.

 

One item they are still looking into is if it relates to the version of the Data Gateway. I don't understand that relationship to the issue but an avenue they are also looking at. I will update again when I hear from Support this morning.

@Anonymous  Sounds like you've made more progess than me - so far I've been advised to update the gateway (the dataflow doesn't use one as it's connecting to Azure SQL DB, plus the dataflow itself refreshes fine anyway) and update Power BI Desktop (we already have the latest version and it all refreshes fine through Power BI Desktop anway). Do you mind sharing your case number so I am able to advise Microsoft support that our cases may be similar?

 

I've noticed that problem datasets in my own "My Workspace" seem to refresh ok today (they didn't yesterday), but identical ones published to our premium workspaces do not. Are your datasets on a premium workspace?

Still broken on my end. How's it going for you all?

NickSter
Advocate I
Advocate I

I have exactly the same problem. Datflow is refreshing fine, but the dataset that uses the dataflow gives this error on refresh. I will also contact support about this. For the time being i am refreshing it manualy through PBI desktop and re-publishing it.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!