Data Source Error

[Mat42]

We're getting the following error message when refreshing a report on PBI service:

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 = \"dbo\", Item = \"This_is_my_Table\"]"}},{"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: This_is_my_Table.

We've tried everything to fix it, including ensuring that the privacy settings for the dataset are the same for both the desktop data and the online gateway connection.

This morning we created a very simple new report containing only the table listed in the error above (anonymised to 'This_is_my_Table'), so we could ensure that there was nothing else that could be conflicting with it, and the error persists when we try to update the report online.

It's has a gateway connection that all seems to be working fine, but the error message is so non-intuitive that I can't tell if it's trying to tell me it's broken. 

I know this error pops up a lot, but have tried everything I've found online and now I'm asking you guys.

[Mat42 · Accepted solution]

So, this is sorted. And it was a really stupid issue.

The gateway connection was created fine: the data credentials were correct, the database was working nicely, everything was correct...but the issue persisted.

Turns out, our IT department had linked the database to the required service account as requested but  they hadn't given it any permissions. So the system was reading everything correctly because the credentials were right, it just didn't have permission to do anything once the credentials were used.

But, at least it works now.

[pmreis]

Hi @Mat42 

Likely you made a modification to the table name when posting, but just in case, can you confirm if the table name matches exactly "This_is_my_Table"?

Also, can you check  if the schema (columns, data types) of "This_is_my_Table" in the source database matches the schema in your Power BI Desktop model?

Otherwise, I would check the gateway connection and the datasource credentials.

Let me know if any of these was the problem.

Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn

[Mat42]

@pmreis Thanks for the advice.

I'll check, but the table schema should be fine. For this latest test we've done nothing but pull the data in, add a table visual to the report so we can publish it, and published it to service. No data transformation, just basic checking that it looks OK. It then immediately won't refresh.

Our other test reports are throwing up the same error and the data has been transformed to vary different degrees, i.e. we've removed columns on some, we've changed all data types on others, but this error message still persists.

The gateway connection might be an issue...but I don't know how to fix that. The database as been linked to the service account we're using, the credentials for the service account work and the connection is created fine. We have other connections using the same server and service account that work fine, it's just this one database that is causing us a problem.