I created a Power BI file using multiple Excel files in Sharepoint as a data source (Get Data -> Sharepoint folder -> combine Excel files). The Excel files have the query uploaded as data model.
Now if I manually open the Excel files and refresh data on them, my Power BI file get updated data. But what if I'd want to update data directly from Power BI? I'd need to schedule the refresh in Power BI to avoid to manually open and update the Excel files. I thought that with the data model I'd get this feature but it doesn't seem to work. Is there a way to do this or the manual update of the Excel files is mandatory?
Solved! Go to Solution.
Do you connect to SharePoint online folder or folder of on-premises SharePoint site in Power BI Desktop? Do you connect to other data sources in your Excel file?
If you connect to folder of on-premises SharePoint site, you would need to install gateway in order to set schedule refresh for your dataset.
Thank you for your reply.
I'm connecting to SharePoint online folder and the Execl file I'm connecting to is connected to CSV files in an FTP folder. I'd need to have the Power BI service to automatically get the updated data without manually opening and update the Excel file.
Hope you can help me with it.
I've been testing Power BI recently to offer powerful KPI to my project team "on top" of their usual Excel reporting files, stored on our Project Sharepoint.
I read many articles stating that in this type of configuration, the PowerBI reports should be updated automatically as soon as the data is updated in the Excel stored on Sharepoint (maybe with 1 hour delay...), and this is not my case.
Let me give you some more details on the set-up I'm using:
1- The data used for this dataset/report is basically a TABLE declared in the Excel workbook
2- The Excel workbook is stored in a Sharepoint (equivalent to a Business OneDrive)
3- I built the report/dataset in Power BI Desktop, performing a GET DATA and using the EXCEL connector (then browsing thru my company Sharepoint folders and connecting to the right Excel workbook
4- Then I published the report to Power BI services, no issue until that point.
In term of data update:
- In Power BI Desktop, I don't expect to have any automatic refresh (when I edit the report agin for example. But when needed, I can refresh the DATASET pressing the REFRESH button --> no issue.
- In Power BI Services, clicking on the Refresh button in the Report view just doesn't trigger any action: no update, no error message...
But when asking for a DATASET refresh, I just receive an error message starting with "Refresh failed due to gateway configuration issues".
Do you have any idea of waht I missed? I really thought that on this type of set-up, the dataset stored in Power BI Services was refreshed automatically from the Excel data stored on ONEDRIVE Business/Sharepoint...
Thanks for your guidance,
PS: I'm still using the Power BI Pro evaluation period...
Find out who's part of the program this season, and welcome the new Super Users.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Learn about the exciting things that happened in July.
All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.
Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.