Data Refresh Error in PowerBI Workspace

[Anonymous]

Hi Everyone,

I have been getting this data refresh error in one of my reports in the powerbi service. I am able to manually refresh in powerbi desktop and in powerbi service but the scheduled refresh keeps giving error. It seems the error is due to merging a table from a sharepoint site and SQL data warehouse in the report.

I have updated my powerbi desktop to the latest version and also ensure the privacy levels for both data connections are set to "Organizational"  and also in the powerbi service but still getting this error.

It disappears when I download the file, re-authenticate, then re-publish... but understandably not the most productive process!

Any tips would be greatly appreciated! Many thanks.

[Anonymous · Accepted solution]

Hi @Anonymous 

This error should be caused by the the Data Souce name didn't completely match (different cases) between the queries and the gateway, because the power BI could not find a matching pair of Table for the Dataset.

You can open the Advanced Editor in the power query and check the Data Source name and table name in the second line. Make sure that the Data source name and Table name referenced here match of a Data Source in the Enterprise Gateway.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for all the suggestions but its still haven't worked!

I would like to give more context to the issue. 

I have a report that has a query that contains historical data saved in a sharepoint file and another query containing latest data pulled from SQL Server. They both have same columns and are appended to a table in the report. 

I am able to manually refresh the report in powerbi desktop and service but the automated data refresh using data refresh schedule keeps giving me this error

"

An error occurred while processing the data in the semantic model.

Refresh failed:

ASC Netcall Dashboard has failed to refresh.
Failure details: The last refresh attempt failed because of an internal service error. This is usually a transient issue. If you try again later and still see this message, contact support.

{"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":"<ccon>SharePoint: Request failed: The underlying connection was closed: An unexpected error occurred on a send.</ccon>"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourceKind","detail":{"type":1,"value":"SharePoint"}},{"code":"Microsoft.Data.Mashup.ValueError.DataSourcePath","detail":{"type":1,"value":"<ccon>https://officesharedservice.sharepoint.com/sites/BI-Corporate</ccon>"}},{"code":"Microsoft.Data.Mashup.ValueError.Reason","detail":{"type":1,"value":"DataSource.Error"}}],"exceptionCulprit":1}}} Table: Agent Summary Breakdown"

I would appreciate if anyone has had this issue and got a permanent solution.

[Anonymous]

Thanks @Expiscornovus. I have retrieved a table from sharepoint and appended with another table from SQL Server. The issue is that am not able to auntomatically refresh due to the table from the sharepoint site as it gives error as stated in the image atatched earlier.

[Expiscornovus]

Just wanted to double check what implementation version and viewmode you used for example. This will help troubleshooting and reproducing your issue.

And do you have any specific setup for the account which is used for the connection (MFA, Conditional Access Policy, etc.).

[Expiscornovus]

Hi @Anonymous,

Can you share a bit more details about your setup? In the first part you mention your retrieve a table from SharePoint, are you using the SharePoint.Tables for that?

https://learn.microsoft.com/en-us/powerquery-m/sharepoint-tables

Or are you using a different Power Query M function to connect to your SharePoint data source?