Power BI Service Refresh Fail - SharePoint: Request failed, DataSource.Error; (works from HardDrive)

[sesilver]

I'm having difficulty refreshing a data file through the Power BI Service due to a constant failure with pulling data from one particular Excel file stored on my OneDrive; the refresh doesn't work either manually or scheduled. 

The refresh works though in Power BI Desktop after I refresh the previews of all the tables in Power Query. Furthermore, the online service does work if I try to pull data from a direct copy of that same Excel file from my hard drive instead via an On-Premises Data Gateway; since the copy that keeps failing is stored on the cloud though, I wasn't expecting it to be a gateway issue. 

Based on other posts, I've already tried clearing and re-entering the permissions for each data source. I've also tried moving the file to different locations on my OneDrive and SharePoint to no avail; I should note that I'm successfully pulling data from other Excel files that are stored in the same directory as the failing Excel file. 

The online service only provides the following error below, so I don't know how to narrow it down to something particular with the file. Again, since the online refresh works when that very same file is pulled from my hard drive, I don't know if it's the file's contents that are what's causing the error.

Has anyone encountered this issue before with accessing files from cloud services?

Underlying error code: -2147467259
Underlying error message: SharePoint: Request failed: https://<company>-my.sharepoint.com/personal/<folder-path>/Expense Log_xlsm/_api/contextinfo
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.DataSourceKind: SharePoint
Microsoft.Data.Mashup.ValueError.DataSourcePath: https://<company>-my.sharepoint.com/personal/<folder-path>/Expense%20Log_xlsm/_api/contextinfo
Microsoft.Data.Mashup.ValueError.Reason: DataSource.Error
Cluster URI: WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID: 6eb10a26-11ef-4c23-b2d3-3ff55bcaf5a5
Time: 2020-12-17 23:11:31Z

-----------

EDIT/UPDATE - I've slowly walked through the Power Query steps and identified which step is causing the problem. The issue stems from where I try to perform a LEFTJOIN of a merged table from the Excel file with a table from a MySQL database. Power BI service is accessing the MySQL database through the On-Premise Gateway I setup. Since accessing the Excel file found my hard originally made use of that same gateway, I’m guessing that’s why there wasn’t an issue before. 

After some tinkering, I managed to fix the issue after reading this Topic Post

[sesilver · Accepted solution]

I've slowly walked through the Power Query steps and identified which step wascausing the problem. The issue stemmed from where I was trying to perform a LEFTJOIN of a merged table from the Excel file with a table from a MySQL database, which was being accessed through an On-Premise Gateway I setup. Originally since I was accessing the Excel file found my hard through that same gateway, that’s probably why there wasn’t an issue before. 

After some tinkering, I managed to fix the issue after reading this Topic Post; Under Gateway Cluster Settings, select Allow user's cloud data sources to refresh through this gateway cluster > Apply

[collinq]

HI @sesilver ,

Well done on finding the solution.  Can you mark your post as solved so that we can use the information in the future if needed and so that folks don't try to give you answers when you already have them.  🙂  Thanks.