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

[Anonymous]

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!

[v-xicai · Accepted solution]

Hi @Anonymous ,

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.

Best Regards,

Amy

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