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

[nick_Lee]

Hello,

I have created a report off of a data set built from queries to an excel file stored in SharePoint. The excel file itself uses a power query to collate and modify data gathered from several other excel files that are also stored in SharePoint. I would like the visuals in the report to update daily to reflect the data for that day. To that end, I have set my data set to refresh daily.

Will my dataset refresh be able to go directly to the sources my excel file queries? I've read some tutorials that seem to suggest this functionallity of bypassing the collated excel file, but I feel that I am misinterpretting the literature.

If this is not the case, I would need to manually open the excel file, refresh its query, then save the file back onto SharePoint in order to update my report. This is not optimal. What would be the best pathway for achieving a automatically refreshing report with these circumstances?

Thanks.

[nick_Lee · Accepted solution]

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.

[v-yuezhe-msft · Accepted solution]

@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

[nick_Lee · Accepted solution]

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.