OneDrive and SharePoint auto-refresh

[Christophe]

According to the documentation, the datasets linked to a OneDrive or SharePoint Excel file should autorefresh every hour.

This works fine for me when I only use the service and the "connect" to Excel feature.

However, I want to do the preparation in a pbix file. In this case, what is the equivalent of the service "connect to Excel"? I tried using various connectors (SharePoint folder, Web), they all establish a proper connection to the file but after I publish to my workspace no refresh happens.

[mahoneypat]

You need to go to the settings for the Dataset you published (when you publish a report, the dataset gets published too), and click on Scheduled Refresh to add the times of day you want it to refresh.  Since you are using cloud sources (OneDrive/Sharepoint), you don't need to worry about a Gateway.  See this link for more details

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-scheduled-refresh

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Christophe]

@mahoneypat thanks for the reply but this is not what I am looking for.

Scheduled refresh allows up to 8 refreshes a day. What I want is the OneDrive/SharePoint autorefresh that happens every hour.

[mahoneypat]

I don't have much experience troubleshooting that refresh. However, another option is to set up a flow that has one hour recurrence and triggers your data set refresh. 

Regards

Pat

[edhans]

That won't work @mahoneypat . Flow based refreshes can be anytime you want, but it is limited to how many refreshes you can do - 8 per day for Pro, 48 per day for Premium. After that, just stop working until the next day.

@Christophe See this article for configuring ONeDrive Refresh. - you still have to go into the settings to set it up. 

[Christophe]

@edhans thanks for the reply and great point about Power Automate limitations.

The issue is that I don't even see OneDrive Refresh in the settings after I publish my pbix file. Could you tell me what method you used to connect to the Excel file when creating the pbix?

[edhans]

I never use OneDrive refresh @Christophe

I'm good with the 8 times a day for a scheduled refresh - that is once every business hour, and I can control the refresh. Plus, I rarely have one file in my PBIX connection. Usually it is a mashup of different sources.

[Christophe]

@edhans ok. My post is specifically about the auto-refresh.

@v-kelly-msft Interesting idea. Could you detail the steps? If I understand correctly, you are saying that I should upload both the xlsx file and the pbix file to SharePoint, and then use the service to connect to the pbix file? And then, when the user updates the xlsx file, both the pbix file and the service will auto-refresh?

[v-kelly-msft · Accepted solution]

Hi @Christophe ,

Yes correctly,but do remember if you update the file locally,you need first to make a refresh in sharepoint or onedrive to make the files updated in the cloud,then the refresh will be automatically executed in Service.

Note: Your dataset that connects to the file will be synchronized soon after the file on OneDrive, or SharePoint Online, is updated. Any visualizations based on the dataset are automatically updated too.

Here is a similar reference you can refer to:https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-csv-file-onedrive

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @v-kelly-msft / @Christophe

My xlsx files are all stored in Sharepoint of different libraries.
I don't think this is an issue and the pbix file is also stored in SharePoint.
Could you explain more on how to use the service to connect to the pbix file so that I can have auto-update, at least once in an hour?

[Christophe]

Thank you @v-kelly-msft for the help.

I successfully tested that if I use Power BI service to import an Excel file from OneDrive or SharePoint, then the OneDrive refresh is set up. However that doesn't allow me to transform the data.

To transform the data, I have to get the Excel file from OneDrive or SharePoint to Power BI desktop, then upload to Power BI service. In this case, the OneDrive refresh is not enabled and only scheduled refresh is available.

[v-kelly-msft]

Hi  @Christophe ,

If you need an auto refresh ,my suggestion for you is to upload your .pbix file to onedrive business or sharepoint,then connect it via service.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[edhans]

You have to go into the Schedule Refresh settings for the report's dataset. See this article for details.