Online Excel File and Auto refresh

[rachaelwalker]

I have a pbix file with 3 data sources. 2 SQL server connections and 1 online excel file. I have the SQL servers configured on our On-premises gateway and auto-refresh works as expected. However, the excel file continues to fail. The privacy settings are the same for both desktop file and data source on gateway. I authenticated successfully on the power bi desktop but it is failing to authenticate on gateway. 

From what I am reading, am I correct to say it is not possible to auto refresh an online excel file imported into power bi using "web" data connector? Are there known workarounds? For this specific dataset, I am thinking I will ask the user to maintain a SharePoint list as opposed to an excel sheet but would like to confirm the limitation of power bi auto refresh and online excel sheets. Thank you

[BA_Pete · Accepted solution]

Hi @rachaelwalker ,

It think it depends on where the actual file resides.

I guess it's either SharePoint, OneDrive for Business (which is also SharePoint), Teams (which is also SharePoint), a network folder, or a local HDD.

For any SharePoint sources you can use the web connector, no problem. I don't think you even need to set this up as a source on your gateway as these all reside within the MS universe.

For network files, you need to use the full true path of the file, and also set the network destination as a source on your gateway (which must also have authentication to access that location on your network). The full true path would be \\server\true drive name\normal file path.

You will not be able to refresh using an Excel file on a local HDD. If this is the case, move it to either a SharePoint-based location, or to a network location that your gateway can access.

Pete

[rachaelwalker]

@BA_Pete it resides on sharepoint online document library. I removed it from gateway and rechecked my credentials and it is now working. Thank you for taking the time to explain. Very informative!