Power Bi data query to excel sheet on sharepoint with its own query

Hello,

I have a report with a dataset that is built from a data query to an excel file stored in SharePoint. This excel file has its own power query that collates and modifies data from several other excel files also stored in SharePoint. I want to create a report that refreshes daily so that the visuals update with the data for that day. To this end, I have set the data set to refresh daily.

Will my data set refresh be able to use the excel file's data connections to gather data from directly from the several other excel files? I've read some tutorials that suggest this functionality, but I feel that I am interpreting the literature incorrectly.

If this is not the case, what is the best pathway to set up an automatically refreshing report for my circumstances? Currently, the excel file must be opened and its query must be manually refreshed; then the file must be saved back onto SharePoint. 

Thanks.

nick_Lee,

Do you store all the excel files in SharePoint online? 

If so, firstly, please use the New Query->From Other Sources->From web option in the excel file to connect to other Excel files, and ensure that you choose "Add this data to data model " when importing data from other Excel files.



Secondly, connect to the Excel file in Power BI Service, go to settings and find your dataset, you can schedule refresh for your dataset. This way, you don't need to manually open the excel file, refresh its query, then save the file back onto SharePoint in order to update the report.  Report in Power BI Service will get updated based on the refresh schedule you set when you change data in excel files.


Regards,
Lydia