cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PBInath Regular Visitor
Regular Visitor

Automatic refresh to excel (on sharepoint) without using personal gateway

Dear fellow users,

 

I am trying have my excel file refreshed automatically to my model but without using my personal gateway. I see using the personal gateway as a less suitable options as it requires my device to be switched on.

 

Do you know whether this is possible? The excel file is saved on an organizational sharepoint location, but if a different option would be easier, please say so!


The only requirement I have is to have the excel file saved on a location and have this refreshed periodically. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Automatic refresh to excel (on sharepoint) without using personal gateway

Hi @PBInath ,

 

There are several scenarios you can considerate:

 

1.If your excel file is saved in SharePoint on premise, then it needs to use the gateway and configure schedule refresh, see more: :On-premises data gateway , Configure scheduled refresh .

 

>>I see using the personal gateway as a less suitable options as it requires my device to be switched on.

 

For this concern, recommend you install on premise data gateway (Enterprise gateway) instead of Personal gateway.

Then you can create high availability clusters of On-premises data gateway installations .The Power BI service always uses the primary gateway in the cluster, unless it’s not available. In that case, the service switches to the next gateway in the cluster, and so on.

You can see how to create gateway cluster: https://docs.microsoft.com/en-us/power-bi/service-gateway-high-availability-clusters , https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem.

 

2..If your excel file is saved in SharePoint on line and your report dataset doesn't contain on premise data source, that is to say the dataset only has the cloud data souce then it needn't to use the gateway.

you can manually refresh the dataset by using Refresh now or you can set up a refresh schedule by using Schedule Refresh. Power BI automatically connects to the workbook on OneDrive, or SharePoint Online, about every hour to check for updates. If the workbook has changed, Power BI will refresh the dataset and reports in the Power BI service.

 

3.If your dataset contains not only the excel file from SharePoint on line but also the on premise data source, then it needs to use the gateway and configure schedule refresh. Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.

 

1.png

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 REPLY 1
Community Support Team
Community Support Team

Re: Automatic refresh to excel (on sharepoint) without using personal gateway

Hi @PBInath ,

 

There are several scenarios you can considerate:

 

1.If your excel file is saved in SharePoint on premise, then it needs to use the gateway and configure schedule refresh, see more: :On-premises data gateway , Configure scheduled refresh .

 

>>I see using the personal gateway as a less suitable options as it requires my device to be switched on.

 

For this concern, recommend you install on premise data gateway (Enterprise gateway) instead of Personal gateway.

Then you can create high availability clusters of On-premises data gateway installations .The Power BI service always uses the primary gateway in the cluster, unless it’s not available. In that case, the service switches to the next gateway in the cluster, and so on.

You can see how to create gateway cluster: https://docs.microsoft.com/en-us/power-bi/service-gateway-high-availability-clusters , https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem.

 

2..If your excel file is saved in SharePoint on line and your report dataset doesn't contain on premise data source, that is to say the dataset only has the cloud data souce then it needn't to use the gateway.

you can manually refresh the dataset by using Refresh now or you can set up a refresh schedule by using Schedule Refresh. Power BI automatically connects to the workbook on OneDrive, or SharePoint Online, about every hour to check for updates. If the workbook has changed, Power BI will refresh the dataset and reports in the Power BI service.

 

3.If your dataset contains not only the excel file from SharePoint on line but also the on premise data source, then it needs to use the gateway and configure schedule refresh. Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.

 

1.png

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 307 members 3,209 guests
Please welcome our newest community members: