Schedule Refresh for Excel Sharepoint Files.

[drallam]

Hi all,

Sorry for posting this type of question. I have researched a lot and found multiple posts and especially this docuemntation from PowerBI itself. Documentation , Sharepoint Post Post 2 but the answer was not conclusive. Is there a way to schedule refresh the excel data from sharepoint just like we do for SQL servers. 
I have a IT admin who manages the Enterprise Gateway. Would I need a personal gateway. 
Or just a thought can we extract data from excel and  convert to a query table and stage the data in SQL servers.
Any input will be great.

Thank you!

[v-sihou-msft]

@drallam

As mentioned in the document, if you directly connect the Excel workbook hosted on SharePoint Online, you don't need Gateway. But if this Excel workbook connects on-premise data sources, you need to make sure Power BI can connect the on-premise data sources so that Gateway is required. If this on-premise data source is not supported by On-Premise Gateway (Enterprise Gateway), you have to use Personal Gateway.

Regards,

[drallam]

@v-sihou-msft

Can you please elaborate this statement "But if this Excel workbook connects on-premise data sources, you need to make sure Power BI can connect the on-premise data sources so that Gateway is required." 

I tagged you in another post of me using the Sharepoint Online Excel files. What different shoudl I do to connect the Excel Files to a on-premise Data Sources, do you mean Sharepoint On-Premise instead of Sharepoint Online as @GilbertQ mentioned?

Thanks for your input.

[GilbertQ]

Hi @drallam

You can schedule a refresh using SharePoint files, it just depends if your SharePoint is On-Premise or cloud version.

What you can try is in Power BI Desktop under Get Data you can use the SharePoint Files to get your files and point that at the high level folder.

Then you can create a data source in the Gateway in the Power BI Service that points to the same location.

Just one note is that if in your Power BI Desktop file you have data from SharePoint Online (Cloud Based) and SQL Server (On-Premise) you will for now have to use the Personal Gateway in order to successfully refresh. This is because currently the On-Premise Gateway only supports either Cloud or On-Premise data sources and not a combination.

[drallam]

 @GilbertQ @v-sihou-msft

So, here's my setup. I have a folder in the Sharepoint website and got files in it.
So, my data source is https://company.sharepoint.com/sites/Myfolder in the data source settings.
I was able to schedule refresh and i think it didnot require a Gateway connection. So I would assume I'm using Sharepoint Online (Cloud Based)
What if I switch to a Sharepoint On-Premise(enterprise), would I be able to use the on-premise gateway connection for both SQL and Sharepoint in the same .pbix file?