0

Environment

  • SharePoint Online
  • Excel 2013 with Power Query Add In

Desired Behaviour

When I click 'Refresh All Connections' in a spreadsheet file in a SharePoint folder:

enter image description here

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.

Actual Behaviour

Connections can be refreshed when 'Open in Desktop App', but the following error occurs when refreshing in SharePoint:

enter image description here

Steps To Reproduce

  1. Open spreadsheet (in SharePoint folder) in Desktop App
  2. Add connections and queries via Power Query tab, eg:

enter image description here

  1. Enter SharePoint site URL and Organisational Account details
  2. Refresh connections and see that everything works (it does)
  3. Save and close file in Desktop App
  4. Click 'Resume Editing' in web browser (and refresh page)
  5. 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):

enter image description here

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:

enter image description here

enter image description here

Edit:

If the following post from 2017 is still true, it seems Excel files in SharePoint cannot handle Power Query connection refreshes:

https://sharepoint.stackexchange.com/a/225685

Another Edit:

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.

1

I don’t think this issue has ever been fixed. It also happens when we use an Excel Web Part on a page.

The only workaround in the mentioned document is to use Excel client application (in the last section):

If you run into a situation where you can’t refresh the data in a browser window, try opening the workbook in Excel.

| improve this answer | |
  • Do you know what alternative approaches are available to show tables and charts that load live data from SharePoint? Anything out of the box in Microsoft 365 would be good as environment is in corporate setting where you can’t just purchase, install and run something. – user1063287 Jun 22 at 9:59
  • 1
    @user1063287 There is a Quick Chart web part in SharePoint that might be able to do the trick: support.microsoft.com/en-us/office/… / If you need anything more professional and complex, here is a workaround with Microsoft Power BI and a third-party tool called AxioWorks: axioworks.com/2019/12/… – Chelsea_MSFT Jun 23 at 7:42
0

Click on the OK dialog button(security warning) is not the issue, in fact that is way it should be,that indicates that the connection is trusted.

In order to fix this, you may try the below steps :

  1. Go to the Data tab from the excel ribbon.
  2. Control how or when the connection is refreshed   On the Data tab, click the arrow under Refresh All, and then click Connection Properties.
  3. In the dialog box that opens, choose the options you want on the Usage tab, under Refresh Control.

Note :

  • Some refresh operations require more steps, as detailed in the following sections. If you see your data source listed, review the corresponding section.

For more details refer to the below article :

Refresh an external data connection in Excel

| improve this answer | |
  • The connections can be refreshed as expected in Excel 2013, however refreshing in SharePoint Online causes error. I have added existing Connection properties screenshots to original post. – user1063287 Jun 20 at 7:16
  • Select the checkbox to refresh every <n minutes( for example 5) > minutes and try. – Habibur Rahaman Jun 20 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.