Excel Refresh in Power BI

[ritasweidy]

Dear Sir, 

I have an excel found on one drive that is shared with others and i implemented a power bi report 

1- can i change the location of the file without the need to re-do the report since it seems that i added the local file not the online file

2- on the one drive , if another user changes the data in the excel the power bi report will refresh 

3- if i put the auto refresh , it is not working 

What is the best solution to connet to excel from power bi in order to refresh automatically and for other users to be able to change the excel ?

Regards

[Anonymous · Accepted solution]

@ritasweidy,

Firstly, it depends on that if you connect to the Excel source in Power BI Service or Power BI Desktop. If you connect to Excel in Power BI Desktop, you are able to change the location of the file without re-doing the report, this source can be updated in Advanced Editor.

However, if you directly connect to Excel in Power BI Service and create the report, it is not possible to change the location of the file without re-doing the report.

Secondly, if you want to make the OneDrive hourly Refresh work for your dataset when changing data in Excel file, you would need to directly connect to the Excel file in Power BI Service. Or you can create report in Power BI Desktop by connecting to Excel stored in SharePoint online or OneDrive, then publish PBIX file to Power BI Service. In this case, OneDrive hourly refresh works for PBIX file. But you can still set schedule refresh for the dataset, this way, when data is updated in the Excel, Power BI report will be updated based on the refresh schedule.

Regards,
Lydia

[Aron_Moore]

1 - Changing the location of the file will break the query. If you look at the editor it'll explicitly show the path of the file.

The excel files we use are in sharepoint/one drive (we build the report pointing to that file/location)

2 - Yes, files stored/linked in sharepoint/one drive will update Power BI reports when the Power BI report is updated.

3 - It's probably not working because of the above points. If you are checking the browser immediately, maybe try a browser refresh.

What we have found to work:

Store excel in the sharepoint/one drive associated with the workgroup

Build report using stored file as datasource

Have a system user for sharepoint refresh credentials

Schedule and/or manually refresh