Enterprise Gateway with Excel - cannot connect dataset

[grapefruitmoon]

Hi all,

I've been having this issue this morning. I have a Power BI report I created in Desktop. It pulls data from an Excel workbook that sits on a fileshare. It also grabs data from SQL Server, but this does not need to be refreshed, and the refresh is disabled. I've published the report to the service, and I'm trying to set it up for manual and scheduled refresh. However, I get the following message:

We couldn't refresh because your data gateway (Power BI – personal) is offline. Make sure your computer is on and the gateway is up and running.

I've got Enterprise Gateway set up with a connection to the Excel workbook on the fileshare, using the UNC path. When I try to schedule the refresh using the gateway, it doesn't see it and wants me to install Personal Gateway instead.

I've also followed the detailed instructions here: https://www.skylinetechnologies.com/Blog/Skyline-Blog/September_2016/RefreshingPowerBIReports - but no luck.

I get the feeling I'm missing something obvious. Does anyone have any ideas? My other option is to re-create the workbook and reports using OneDrive For Business as a source, but that seems a bit like a workaround (in addition to a fair bit of re-work).

Thanks in advance!

[Seth_C_Bauer · Accepted solution]

@grapefruitmoon I think the key piece here is "It also grabs data from SQL Server, but this does not need to be refreshed, and the refresh is disabled." - What do you mean this is disabled?

Either way, I'm willing to bet that because you have this on-premises data source connection that Power BI is recognizing that and requiring you to set up a gateway because the SQL source isn't configured. And, it is an on-premises data source, so it requires a gateway.

[grapefruitmoon]

Thanks for the reply @Seth_C_Bauer - and I think you're totally correct. I've done a little test, using the UNC Excel data, but not using the data from SQL Server. And it works! I can now hook up to the Enterprise Gateway and refresh the data. However, if I set up a Gateway connection to the SQL Server it still doesn't work, so looks like you can't mix Gateway sources. More testing needed there.

The data extracted from the SQL Server is one-off static data, so not a problem for me to remove the connection and put the data in Excel itself instead.

Thanks for your help - banging my head as I should have sussed this one earlier! Nice one.