0

It is the first time that I am using the web version of excel with external data sources on Sharepoint so I hope that you can help my with that.

I do have an Excel list that grabs data from an on-premise SQL server within the network using Power Query. I copied this file to Sharepoint so that several people can refresh the data and filter the columns. However, when I try to refresh the data in Excel Online, I get the following message:

"We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh..."

For me, it looks like Excel is not able to connect to the on-premise SQL server. Is there a way to use a certain gateway like in Power BI or are there any other things I could do to refresh the data online?

Thank you for your help!

Improve this question

1 Answer 1

Reset to default
0

Here is a tutorial for refreshing data from an on-premises SQL Server database using Power BI: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial

And to work with SharePoint Online, you can either:

  1. Get data from SharePoint Online lists in Power BI;
  2. Use the on-premises data gateway in Power BI that merge or append on-premises and cloud data sources, which seems to be an unfinished feature that does not provide full function to work with both sides (See the reference here).

Another post for your reference: Connection Error On Premises Gateway - SQL Server and Sharepoint (O365).

Improve this answer
2
  • I do not know whether I explained my question good enough. I would like to have a Power BI visualisation of my SQL table data on the one hand and on the other hand, I need a tabular analysis in Excel (due to the formatting options). There is no SharePoint source. Do you still know how to solve this problem or is there a better way to display the data than in Excel?
    – Benedikt W
    Jun 18, 2020 at 11:04
  • @BenediktW The easiest way would be using Power BI directly to connect to on-premise SQL server, and of course you can export the data to Excel for further analysis later: docs.microsoft.com/en-us/power-bi/visuals/…, but I don’t think you can carry the data connection to Excel either.
    – Chelsea_MSFT
    Jun 19, 2020 at 6:08

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.