Forum Discussion
Automatic Refresh not working when connecting to SharePoint Online file
shampayne,
Based on your description, you want to configure OneDrive refresh for your dataset. And you want to get updated data in Power BI report when you change data in Excel source within one hour.
In this case, you would need to directly connect to the SharePoint online Excel file in Power BI Service using Get Data->Files->SharePoint option. Then create report in Power BI Service and configure OneDrive Refresh for the dataset.
Regards,
LydiaThank you for the responses. This is truly helping understand the capabilities and limitations of this very valuable feature.
I have done some initial tests based on the feedback from this thread and have gotten it to work successfully in some scenarios.
Scenario 1 (working)
File Configuration
- Created an Excel spreadsheet in Excel Online. Saved to a Sharepoint document library. *NOTE* This spreadsheet MUST be formatted as a table to work.
- Created a PowerBI Report directly in the PowerBI Service website
- Clicking My Workspace > +Create in the top right corner > Report > Import New Data > Files > Get > Sharepoint - Team Sites > Paste in Sharepoint URL > Connect > Load the newly created spreadsheet that's saved in the Sharepoint document library
PowerBI Setup
- After loading in the above, I created a handful of visualizations in the Dataset and the saved as a new report
- Checking the Schedule Refresh option of the Dataset shows that OneDrive refresh is enabled. Note that Scheduled Refresh was not an option
Testing
- Went back to the Excel spreadsheet that is saved on Sharepoint Online and added some random data using Excel Online
- Checked the report about an hour later on the PowerBI Service website and saw that it accurately reflected the changes that were made to the spreadsheet on Sharepoint Online
Scenario 2 (not working)
I did basically the same thing as Scenario 1 only created the PowerBI Report using PowerBI desktop as opposed to PowerBI Service website. I used the exact same Spreadsheet as Scenario 1 saved in the same Sharepoint Online location as Scenario 1. Saved the PBXI desktop file to the same Sharepoint Online document library as where the spreadsheet is located. Then imported the PBXI desktop file to the PowerBI Service using Get Data option. This shows that OneDrive refresh is enabled but it will not pick up the changes when the Excel file is changed in Excel Online while Scenario 1 does. Checking the refresh history shows that the Dataset in this scenario has not been refreshed.
TLDR; It appears that when a report is created using PowerBI desktop, automatic refresh does not work even though PowerBI Service shows that OneDrive refresh is configured correctly and enabled. Everything works if the report is created using PowerBI Service (which is not an option for many of us).
Thank you for the responses. This is truly helping understand the capabilities and limitations of this very valuable feature.
I have done some initial tests based on the feedback from this thread and have gotten it to work successfully in some scenarios.
Scenario 1 (working)
File Configuration
- Created an Excel spreadsheet in Excel Online. Saved to a Sharepoint document library. *NOTE* This spreadsheet MUST be formatted as a table to work.
- Created a PowerBI Report directly in the PowerBI Service website
- Clicking My Workspace > +Create in the top right corner > Report > Import New Data > Files > Get > Sharepoint - Team Sites > Paste in Sharepoint URL > Connect > Load the newly created spreadsheet that's saved in the Sharepoint document library
PowerBI Setup
- After loading in the above, I created a handful of visualizations in the Dataset and the saved as a new report
- Checking the Schedule Refresh option of the Dataset shows that OneDrive refresh is enabled. Note that Scheduled Refresh was not an option
Testing
- Went back to the Excel spreadsheet that is saved on Sharepoint Online and added some random data using Excel Online
- Checked the report about an hour later on the PowerBI Service website and saw that it accurately reflected the changes that were made to the spreadsheet on Sharepoint Online
Scenario 2 (not working)
I did basically the same thing as Scenario 1 only created the PowerBI Report using PowerBI desktop as opposed to PowerBI Service website. I used the exact same Spreadsheet as Scenario 1 saved in the same Sharepoint Online location as Scenario 1. Saved the PBXI desktop file to the same Sharepoint Online document library as where the spreadsheet is located. Then imported the PBXI desktop file to the PowerBI Service using Get Data option. This shows that OneDrive refresh is enabled but it will not pick up the changes when the Excel file is changed in Excel Online while Scenario 1 does. Checking the refresh history shows that the Dataset in this scenario has not been refreshed.
TLDR; It appears that when a report is created using PowerBI desktop, automatic refresh does not work even though PowerBI Service shows that OneDrive refresh is configured correctly and enabled. Everything works if the report is created using PowerBI Service (which is not an option for many of us).
Thanks for sharing,
after a few tries I came to the same conclusion, it is pretty frustrating that automatic refresh is exclusively destinated to datasets established on Power BI service. I need the pbi desktop query editor which is not available on web to fulfill my needs and a single scheduled refresh by day is not enough.
I guess we should wait for an update to fix this thread.
Have a nice day all
Thank you all. I am in exactly the same position.
In this forum I was also told, that the hourly automatic Onedrive refresh (after using Power BI dektop before Power BI Service) only works when the Power BI dektop was connect by Direct Query for example to a SQL database.
Seems strange this problem is marked as 'solved'?
Same as the orginal poster, If I've written my reports in Power BI Desktop, and they use an Excel file saved in Sharepoint cloud ( not on premise) as a data source, then it seems there is no automatic, or scheduled, refresh available.
Is this correct?
Is there any way I can open the BI file that was created on the desktop version from the web version, and update it so that it does automatically refresh?
Or is there a work around to this?
