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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shampayne
New Member

Automatic Refresh not working when connecting to SharePoint Online file

Hello, 

 

I am attempting to setup a simple Dashboard to automatically refresh (not scheduled refresh) from two Excel spreadsheets that are saved on a Sharepoint Online document library. The PowerBI desktop file is also saved in the same document library. 

 

According to the Microsoft doc Data refresh in Power BI

"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." 

 

I have been unable to get this working after trying multiple different ways of importing the data to PowerBI and manipulating/saving the spreadsheets. 

 

I have tried using Get Data to import the file as a Web link as well as the Sharepoint Folder connector. In both of these instances, I can successfully connect to the data, publish the dashboard, and can schedule a refresh. So I know that permissions are configured correctly and the links to the spreadsheets are correct since the PowerBI service allows me to both schedule a refresh as well as manually refresh.

 

My understanding from the Microsoft doc linked above is that if my source files are saved on a Sharepoint Online site, the PowerBI dataset will automatically refresh itself if there is a change to the source file; in my case the two Excel spreadsheets. I have attempted changing data in the spreadsheets in both Excel Online and Excel Desktop so that they save back to Sharepoint Online. Based on the description of automatic refresh, PowerBI should look for these changes about once an hour and refresh if it detects a change.

 

That is the part that isn't working. No matter what I do, the dataset will not automatically refresh.

 

Has anyone gotten this working successfully or can link me to a guide to set it up? I have been searching for weeks for examples of this and have been unable to find anything.

 

Thank you! 

2 ACCEPTED SOLUTIONS
v-yuezhe-msft
Employee
Employee

@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.
1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

shampayne
New Member

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).

 

 

 

View solution in original post

12 REPLIES 12
itskool
Helper II
Helper II

Has anyone found a solution to this? I am in the same situation, pulling data from excel files on Shapoint (online), and when data gets added to the excel file, the scheduled/manual refresh runs without errors, but the data is not shown in the report.

shampayne
New Member

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 

lukas1
Frequent Visitor

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? 

 

v-yuezhe-msft
Employee
Employee

@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.
1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I think I have exactly the same problem as the thread starter. 

I really would like to have a hourly refresh (scheduled refresh with 8 times a day is not enough). But I also need to use Power BI dektop to modify the data (cannot do it only online). 

 

So I have one excel file in Onedrive. I import this excel file in power BI desktop and create my charts. I save this power BI dektop file also in the same Onedrive folder. I use power BI online and import the power bi desktop file. (using Get Data->Files->Onedrive Business option). 

 

Now the Problem is when I change data in the excel file nothing happens or gets refreshed. 

(the onedrive option is enabled. If I click on Gateway, Power Bi even says I don't need it because every file is saved in the cloud)

 

As I read in other threads, if everything is in Onecloud it should work, even if I use power BI desktop before the online service. 

(example: https://www.qumio.com/Blog/Lists/Posts/Post.aspx?ID=44)

 

Do you have any idea why it is not working? Thank you very much.

Hi,

 

I'm precisely exactly faithfully in the same situation that Lukas1.

If someone has the solution or even a simple suggestion I'd be glad to read it !

 

Regards.

Anonymous
Not applicable

I suspect the issue is because you chose to make use of Web Link.  I've ran afoul of this issue before.  There are multiple ways to achieve access to items in SharePoint Online and OneDrive.  Unfortunately that "success" can come with unintended consequences.  In this case, because you have used web, the refresh wants a gateway.

 

What we need to do is alter your query such that it going to OneDrive directly and can make use of the direct connection functionality.  This won't cause you to lose any of your work, as we just need to tweak the "Source" line of your import query.

 

Onedrive acts like a SharePoint online folder.  So you can actually just use the SharePoint Online Folder connector.  Unlike other places, you don't need to use the full URL, just enough to get to your specific folder.  Next we use navigation steps to get to your file.

 

Source = SharePoint.Files("https://YOURCOMPANY.sharepoint.com/personal/FULL_NAME_COMPANY_com/", [ApiVersion = 15])

If you use a Source line that looks like that, all you need to do is locate your files name in the name column.  It doesn't matter if its in a sub folder.  Next click on the yellow word "Binary" on that row, now from here it should act just like any other excel file.

 

 

Good morning and thank you for the suggestions.

 

I had previously tried using the Sharepoint Folder connector exactly as you mentioned and it has been unsuccessful. It works fine for scheduled refresh and manual refresh, but it will not automatically refresh as outlined in the Microsoft doc when the spreadsheet changes.

 

The web link is also configured correctly for a Sharepoint Online file. The PowerBI service is not asking for a gateway as this is also working fine for scheduled refresh and manual refresh.

 

Thanks

 

 

 

Anonymous
Not applicable

What do you mean by automatic refresh?  Scheduled refresh is the automated refresh option for an import dataset.  Are you talking about direct query?

Anonymous
Not applicable

Ok i'm wrong here. I've ignored the CSV on one-drive option:

 

https://docs.microsoft.com/en-us/power-bi/refresh-csv-file-onedrive

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors