Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
grapefruitmoon
Advocate I
Advocate I

Enterprise Gateway with Excel - cannot connect dataset

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!

1 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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

View solution in original post

2 REPLIES 2

@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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.