Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

OneDrive Excel workbook automatic refresh - how can I get it to work?

Hi there,

 

I'm a little confused. In this documentation https://docs.microsoft.com/en-us/power-bi/refresh-data it says that, for OneDrive refresh: 

"Unlike a dataset refresh during which Power BI imports data from a data source into a dataset, OneDrive refresh synchronizes datasets and reports with their source files. By default, Power BI checks about every hour if a dataset connected to a file on OneDrive or SharePoint Online requires synchronization."

 

Also similarly in this documentation https://docs.microsoft.com/en-us/power-bi/service-get-data-from-files it says:

 

"OneDrive - Business – If you have OneDrive for Business and you sign into it with the same account you sign into Power BI with, this is by-far the most effective way to keep your work in Excel Power BI Desktop, or a .CSV file and your dataset, reports, and dashboards in Power BI in-sync. Because both Power BI and OneDrive are in the cloud, Power BI connects to your file on OneDrive about every hour. If any changes are found, your dataset, reports, and dashboards are automatically updated in Power BI."

 

This seems to say that, unlike a scheduled refresh which has to be set up, it will happen automatically. I have uploaded a workbook to OneDrive for Business, connected to it in Power BI Desktop using the weblink, then published to the Service. Now if I edit the workbook and save it, it doesn't automatically update the Dataset (it's been a day or two since). However, if I go to the Dataset page and click on Refresh Now, it works fine. 

 

If I go to the Settings page, it doesn't even show the OneDrive Refresh option, which seems to indicate it doesn't see it as a OneDrive Excel workbook, which it clearly is.

 

On the other hand, if I set up a Scheduled Refresh (8 times once per hour - max of Power BI Pro licence), it works fine.

 

Have I misunderstood something? Or do I need to do something different?

 

thanks in advance.

5 REPLIES 5
Anonymous
Not applicable

hi Need help on one drive excel file refresh 

Anonymous
Not applicable

I am using one drive as my data source when connecting get data option in power bi service.

the dataset is refresh when i have changed or deleted or updated some data in one drive source within 10 min.

but in power bi one drive refresh there is a sentence like By default, OneDrive updates files hourly. Do you want your files to be kept up to date? 

the bold sentence is not clear for me .why confusing ?

 

v-cherch-msft
Employee
Employee

Hi @Anonymous 

Please have a look at the document:Refresh a dataset stored on OneDrive or SharePoint Online

'When you store a Power BI Desktop file on OneDrive or SharePoint Online, any data you’ve loaded into your file’s model is imported into the dataset, and any reports you’ve created in the file are loaded into Reports in the Power BI serviced. When you make changes to your file on OneDrive or SharePoint Online, such as adding new measures, changing column names, or editing visualizations, once you save the file those changes will be updated in the Power BI service too, usually within about an hour.'

Some threads for your reference:

https://community.powerbi.com/t5/Integrations-with-Files-and/Automatic-Refresh-not-working-when-conn...

https://community.powerbi.com/t5/Service/Auomatic-Refresh-reports-successful-refresh-but-doesn-t-rea...

Regards,

 

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-cherch-msft 

 

Thanks for the documentation. Can I confirm the following:

 

I have used Get Data/OneDrive for Business/Connect to connect the Dataset from OneDrive (in the PBIX file) to the Power BI Service. I have connected that PBIX to an Excel workbook in my OneDrive for Business folder.

If I make a change to the PBIX, I don't have to click on Publish, because it automatically syncs between OneDrive and Power BI Service.

However, if I make a change to the Excel Workbook (the data source), I need to either (a) initiate a manual refresh and/or (b) set up a Scheduled Refresh (up to 8 times daily maximum). It does not automatically refresh the data source like the PBIX sync above.

Can anyone confirm the last post?  I'm struggling with the same thing.  I have both the .pbix and an excel file which serves as the datasource on One Drive for Business.  I can see that if I change the .pbix file, PowerBI service picks up those changes.  I do not see, however, that changes in the Excel file are picked up and populated through the datasource to the Reports on the Service.  Further - Scheduled Refresh is not an option.  The option is "available" but when I open it to schedule. the button to schedule (and all else within the Scheduled Refresh is greyed out.

 

One last thing...does it matter if I choose "publish" to service, or start at the service and upload the file.  It seems that to upload the file my One Drive subdirectories have to match my Power BI workspaces.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Top Solution Authors
Top Kudoed Authors