cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nick_Lee
Regular Visitor

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

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.

2 ACCEPTED SOLUTIONS
nick_Lee
Regular Visitor

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.

View solution in original post

v-yuezhe-msft
Microsoft
Microsoft

@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.
1.PNG
2.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Microsoft
Microsoft

@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.
1.PNG
2.PNG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

nick_Lee
Regular Visitor

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Kudoed Authors