- SharePoint Online
- Excel 2013 with Power Query Add In
When I click 'Refresh All Connections' in a spreadsheet file in a SharePoint folder:
data from 2 x SharePoint lists and 1 x spreadsheet file (in a SharePoint folder) will be loaded.
Ideally, I would like the data to be updated when the source data changes.
Connections can be refreshed when 'Open in Desktop App', but the following error occurs when refreshing in SharePoint:
Steps To Reproduce
- Open spreadsheet (in SharePoint folder) in Desktop App
- Add connections and queries via Power Query tab, eg:
- Enter SharePoint site URL and Organisational Account details
- Refresh connections and see that everything works (it does)
- Save and close file in Desktop App
- Click 'Resume Editing' in web browser (and refresh page)
- Click 'Refresh All Connections' in web browser which causes the error
What I've Tried
When prompted in Desktop App, i clicked 'Yes' to the following (not sure if that was related to the issue or not):
I also have read the Microsoft article Refreshing data in a workbook in a browser window which doesn't seem to have any solution for enabling data refresh in the browser if it isn't working:
Depending on how your environment is configured, data refresh might not occur. For example, if someone created a workbook that uses secure, external data connections to on-premises servers, those data connections will probably not work in Microsoft 365. If you run into a situation where you can’t refresh the data in a browser window, try opening the workbook in Excel. You can also contact a SharePoint administrator.
For reference, below are the Connections properties for all Workbook connections:
If the following post from 2017 is still true, it seems Excel files in SharePoint cannot handle Power Query connection refreshes:
I also tried checking 'Add to Data Model' after creating the queries and 'loading to' a particular cell, I still get the same error when clicking 'Refresh All' in the browser.