cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sss0379
Frequent Visitor

Sharepoint Online - Data Set Refresh

Hi,

 

I created a visual reports in PowerBI Desktop by connecting to an Excel File in Sharepoint Online. Published it from Desktop to my online Workspace in App Service.

 

Now, my understanding was that the Dataset will be refreshed automatically every one hour as my Datasource is Excel on Sharepoint Online. Unfortunately, it doesnt work that way and only Scheduled Refresh works.

 

Only when I create a new Datasource in PowerBI App Service it refreshes every one hour, but how can I now point this new Dataset to my reports that are published in the Workspace.

 

This is what Microsoft website says: 

https://docs.microsoft.com/en-us/power-bi/refresh-data#excel-workbook-with-tables-of-data

When you connect to a file on OneDrive, or SharePoint Online, your reports and dashboards will show data as it is in the file. In this case, your Excel workbook. Power BI automatically checks the file, about every hour, for updates. If you make changes to the workbook (stored in OneDrive or SharePoint Online), those changes are reflected in your dashboard and reports within an hour. You don’t need to setup refresh at all. However, if you need to see your updates in Power BI immediately, you can manually refresh the dataset by using Refresh Now.

Appreciate your help.

Thanks

1 ACCEPTED SOLUTION

Hi @v-shex-msft

 

Yes thats exactly my point if the Dataset is created in App Service then it has OneDrive Refresh Option at Settings

If the Dataset is created in PowerBI Desktop and published then only Scheduled Refresh is available.

 

Is this meant to be? Why is it so after all my DataSource is Onedrive/Sharepoint Online even then they are treated seperately.

 

If PBIX is added to SharePoint ONline then only PBIX gets changed but my Sharepoint ONline excel file which is pointed in this PBIX  doesnt reflect the changes

 

I dont think so there is a solution to this, this is the way it is designed. i had posted this question to check if my understanding is correct.

View solution in original post

9 REPLIES 9
v-shex-msft
Community Support
Community Support

HI @sss0379,

 

I'd like to suggest you take a look at following link which told about onedrive refresh.

Power BI: Why my report is not refreshing from OneDrive? Compatibility chart for sources.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft

 

Thanks for the link to the article. I read the article and my connection is setup to Excel file on Sharepoint in my Desktop application. Once I publish, my dataset doesnt get refreshed in App Service. I need to do a scheduled refresh.

 

I also tried putting the PBIX desktop file on Sharepoint and referenced this PBIX file from PowerBI App Service, but after an hour it my PBIX changes on Sharepoint are reflected in Reports in App Service but my Datasource changes in Excel file which is also in Sharepoint ONline doesnt change.

 

I want to understand is this the default functionality or do we need to follow any more steps.

 

Thanks

Sonali 

HI @sss0379,

 

Did any connection to other data sources in your excel file?
For normal xlsx files which stored in sharepoint online, power bi can use onedrive refresh handling it.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft

 

No external connection in Excel files. 

 

Just simple 2 columns

 

Col1 || Col2

---------------

1.         1

2          2

3          3

 

Included this file in Sharepoint Online. Set a connection to this file in PBI Desktop. Create a table visualisation which displays this table. Published to PBI App Service. 

 

Now if I add a new row (4 4) in Sharepoint ONline in this Excel file, it does not get automatically refreshed even after 3 days and displayed in the Report/Dashboard in PBI App Service.

 

The only way it gets refreshed is Scheduled Refresh.

 

If I create a Dataset connection to Excel file on Shrepoint online in App Service it gets auto refreshed but not the one that is created in desktop and published on the app service.

 

Is scheduled refresh the only way to do for desktop files?

 

Thanks 

 

HI @sss0379,

 

Can you find onedrive refresh option at settings?
If this option not appears in your dataset, it means you need to manually refresh or setting Scheduled refresh for this datasource.

 

Spoiler

This synchronizes your Power BI Desktop, or Excel, file between the Power BI service and OneDrive, or SharePoint Online. This does not pull data from the original data source. The dataset in Power BI will only be updated with what is in the file within OneDrive, or SharePoint Online.

Data refresh in Power BI

Refresh a dataset created from an Excel workbook on OneDrive, or SharePoint Online

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft

 

Yes thats exactly my point if the Dataset is created in App Service then it has OneDrive Refresh Option at Settings

If the Dataset is created in PowerBI Desktop and published then only Scheduled Refresh is available.

 

Is this meant to be? Why is it so after all my DataSource is Onedrive/Sharepoint Online even then they are treated seperately.

 

If PBIX is added to SharePoint ONline then only PBIX gets changed but my Sharepoint ONline excel file which is pointed in this PBIX  doesnt reflect the changes

 

I dont think so there is a solution to this, this is the way it is designed. i had posted this question to check if my understanding is correct.

HI @sss0379,

 

Actually, Onedrive refresh not works for report which use sharepoint online folder/list connector.

For this scenario, please use web/excel connector with file url to instead.

 

Reference link:

Use OneDrive for Business links in Power BI Desktop

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft

 

I have connected using the steps mentioned in the link you shared but PBIX desktop created auto-refresh doesnt work, seems like this is the way it is.

Hi @v-shex-msft

 

No external connection in Excel files. 

 

Just simple 2 columns

 

Col1 || Col2

---------------

1.         1

2          2

3          3

 

Included this file in Sharepoint Online. Set a connection to this file in PBI Desktop. Create a table visualisation which displays this table. Published to PBI App Service. 

 

Now if I add a new row (4 4) in Sharepoint ONline in this Excel file, it does not get automatically refreshed even after 3 days and displayed in the Report/Dashboard in PBI App Service.

 

The only way it gets refreshed is Scheduled Refresh.

 

If I create a Dataset connection to Excel file on Shrepoint online in App Service it gets auto refreshed but not the one that is created in desktop and published on the app service.

 

Is scheduled refresh the only way to do for desktop files?

 

Thanks 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors