Gateway - The key didn't match any rows in the table

[Speed]

Hi all,

I have a problem with one of our reports. The Report connects to a MySql Database and everything worked well until we added 2 new tables from the same database. From the pbix file the data refresh works without problems but when the Powerbi Service tries to get the data via our on-premise data gateway we get this error:

There was an error when processing the data in the dataset.

Please try again later or contact support. If you contact support, please provide these details.

• Data source error{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The key didn't match any rows in the table."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.Key","detail":{"type":1,"value":"[Schema = \"<db name>\", Item = \"<table name>\"]"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"Expression.Error"}},{"code":"Microsoft.Data.Mashup.ValueError.Table","detail":{"type":1,"value":"#table({\"Name\", \"Data\", \"Schema\", \"Item\", \"Kind\"}, {})"}}],"exceptionCulprit":1}}} Table: <table name>.

The gateway is up to date. I already tried to remove the whole Report incl Dataset and pushed it again, I pushed it to another workspace but nothing fixed the error.

When I remove the 2 tables the refresh is working again. Is there some cached data that doesn't recognize the 2 new tables? How can I fix this?

Best

Alex 

[csolutions]

Are any of the fields of the new tables formatted as date? I had the same issue. Removed all date formats (left as text) and then within the desktop file - reverted back to date format. That seemed to fix my issue. 

[Speed]

Thanks for the response.

Unfortunately not 😞 In the new tables there are only Text and Numbers, no dates.

[v-kelly-msft]

Hi @Speed ，

Delete the data source which you have configured before then re-configure it in service.

Here is the reference.

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

[Speed]

Hi @v-kelly-msft 

thanks for the hint but I get the same error after readding the data source 😞

Best

Alex

[v-kelly-msft · Accepted solution]

Hi @Speed ，

Based on my test, the error message in subject would be caused by missing tables/views, either dropped or the user set in data source don't have access to. Please check the highlighted part in your refresh history to determine what is the problematic table.

Also make sure that you've set the correct user with sufficient premission in the data source of your gateway. If the user is correct, try to delete and re-create the data source. If it still doesn't work, try to save your pbix in another name and publish it as a new report.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Speed]

@v-kelly-msft Thanks for the hint. After a check with the DBA we noticed that he gave access to the new tables to the user readonly@<dev_server> but not the user readonly@<gateway_server>.

Best

Alex

[Anonymous]

Hi I had the same problem.

I got message: UnderlyingErrorMessage","detail":{"type":1,"value":"Information is needed in order to combine data"}}
And it was connected to how I combine the data. In power query I couldnt merge data form table (data from computer disc C) and form different table (form jira or from sharepoint) even privacy level was the same.
I just didnt merge this data in power query, I did it in DAX by "related", and then refresh is working ok.