Forum Discussion
Excel Services and On-Premises SQL Data
If you're talking about Excel Online in Office 365 connecting to on prem data, that is possible with the Gateway if you're using PowerPivot. You can use Power BI to schedule an Excel Workbook refresh from on premises data.
If you're talking about on prem Excel Services or Excel Online, you can configure the data connection to refresh on open.
John White can you share more information on how to configure Excel Online to access on-premise SQL Data. And does that also apply to Mac Office 365 Excel. I have attached an error I received when I tried refreshing excel online that is connected to an On-premise SQL Server
How Excel is connected, Power Query?
SergeiBaklan Yes, I am using Power Query
To my knowledge Excel Online doesn't support PQ refresh. That's use desktop app, or publish on Power BI (file shall have data model) and refresh from it, better use scheduled refresh. Another option is to use Flow to refresh such file.
ambroseJoel - All that you should need is an on-premises data gateway installed, and the data source in question registered. However, the error that you are showing indicated something else, which is odd, but I'm not sure about.
John White is the getaway that you are referring to the same one for Power BI? or do we have another one? and how do you connect it to Excel?
ambroseJoel Yes it is. You will install the On Premises Data Gateway, and then register the data source that your Power Query uses. Once registered, the Power Query should be refreshable. Here's tha main article on the gateway:
On-premises data gateway - Power BI | Microsoft Docs
Here's a community post talking about it as well: Solved: Scheduled refresh for excel file with on-premise g... - Microsoft Power BI Community
