Enterprise Gateway with Excel - cannot connect dataset

[jneijssen]

Dear BI-Community,

I have created a personal report using Power-BI Desktop.  I am using two kinds of data:

- a locally stored Excel file. 

- Data from an SQL Server. 

Everything worked out very nice, the report looks great.  I have uploaded this report in the Microsoft BI Online environment. Now I want this report to update automatically with a specific time setting. For this reason, we have installed an enterprise gateway.  Through this Gateway, I can update the SQL data.  A connection to the Excel file is not possible.

Now my problem: wenn going into the update options of my dataset, I get  The message that my personal gateway is offline. I cannot switch to Enterprise Gateway. 

- I  have created a test report with SQL data only.  This is working perfectly with the enterprise gateway.  Updates are scheduled and executed.

-  my reports with SQL data and Excel file data is not updating through the enterprise gateway.  I assume the reason is that the Excel data cannot be updated yet. 

How can I solve this problem? How can I make sure the report is updated and both Excel file data and SQL data are updated?

I have tried putting the Excel file in a Onedrive environment, but I am doing something wrong.  I cannot link this file to my report, it is not updated.

Can someone help me with a detailed description, what steps I should follow to get this report update? S and I Disha no information, my company does not allow the gateway to access the local network.  I cannot use the gateway to update the Excel file.  They are for it has to be done through another solution (Onedrive?). 

I hope someone can help me! 

Thank you very much, looking forward for solutions.

Kind regards,

Jochem

[v-yuezhe-msft · Accepted solution]

Hi @jneijssen，

When you store Excel file in local disk, after you publish Power BI Desktop file to Power BI Service, have you added the Excel data source under Enterprise gateway? For detailed steps about how to add Excel data source under gateway, please check my reply in this similar thread, ensure that you are using same paths to connect to Excel in Power BI Service and Power BI Desktop.

In addition, when you choose to store Excel file in OneDrive, do you use OneDrive for business? If that is the case, please follow the instructions in this article to connect to Excel file in Power BI Desktop. After you publish your Power BI Desktop file which contains online data source(Excel on OneDrive) and on-premises data source(SQL Server) to Power BI Service, please use Power BI personal gateway to refresh your dataset as enterprise gateway is not suitable for the dataset in this scenario.

Thanks,
Lydia Zhang

[jneijssen]

Dear BI-Community,

I have created a personal report using Power-BI Desktop. I am using two kinds of data:

- a locally stored Excel file.
- Data from an SQL Server.

Everything worked out very nice, the report looks great. I have uploaded this report in the Microsoft BI Online environment. Now I want this report to update automatically with a specific time setting. For this reason, we have installed an enterprise gateway. Through this Gateway, I can update the SQL data. A connection to the Excel file is not possible.

Now my problem: wenn going into the update options of my dataset, I get The message that my personal gateway is offline. I cannot switch to Enterprise Gateway.

- I have created a test report with SQL data only. This is working perfectly with the enterprise gateway. Updates are scheduled and executed.

- my reports with SQL data and Excel file data is not updating through the enterprise gateway. I assume the reason is that the Excel data cannot be updated yet.

How can I solve this problem? How can I make sure the report is updated and both Excel file data and SQL data are updated?

I have tried putting the Excel file in a Onedrive environment, but I am doing something wrong. I cannot link this file to my report, it is not updated.

Can someone help me with a detailed description, what steps I should follow to get this report update? S and I Disha no information, my company does not allow the gateway to access the local network. I cannot use the gateway to update the Excel file. They are for it has to be done through another solution (Onedrive?).

I hope someone can help me!

Thank you very much, looking forward for solutions.

Kind regards,

Jochem