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
zrichardson
Advocate I
Advocate I

OneDrive Excel to OneDrive pbix to Power BI Service

This is my setup:

 

I have an excel document that is updated on OneDrive Hourly. I have a pbix file that is connected to that excel document that is uploaded to my OneDrive. Finally, I have the PowerBI Service connected to that pbix file on OneDrive. The problem is, the OneDrive automatic refresh isn't updating the data inside of the excel file. If I go in and automatically refresh the data scource, the data will then be refreshed. 

 

My question is, how can I get the hourly OneDrive for business automatic refresh to update the data?

18 REPLIES 18
alanhodgson
Solution Supplier
Solution Supplier

Hey @zrichardson,

 

It sounds like you want the data in the Excel file to refresh when the hourly OneDrive refresh happens. I am assuming you are using the "Import" method to create your data model.

 

Here is the limitation:

   - If the Excel sheet has any external connections to a database or another workbook, then you must manually go into the Excel sheet and "Refresh All", before the changes will show in PBI Service.

 

Also, have you configured an on-prem data gateway? If so, personal or enterprise?

 

Hope this helps,

 

Alan

 

 

 

I am not using "Import" because I am not connecting the service directly to the excel file. I am connecting the service to the pbix file on OneDrive. I am connecting the pbix file to the excel file on OneDrive. 

 

I am not using any external data sources in my excel file. I have a scheduler that sends me an updated csv in an email. I use Microsoft flow to save over the previous file. That is how I update the data. 

 

Again, everything updates perfectly when I go and manually "Refresh Now" the data source. However, the data does not refresh on the hourly OneDrive updates.

 

Thanks!

Hi @zrichardson,

I am not quite sure that how you connect to the Excel file from Power Desktop and if you enter credential for the Excel file in Power BI Service. To make the hourly refresh work in Power BI Service, please make sure the following things.

1. Firstly, put your Excel file in a folder on OneDrive for Business, then connect the Excel file via Get Data->Web entry in Power BI Desktop as follows. Ensure that you use organizational account to connect to the file.
1.png

2. After your create reports in Power BI Desktop, save the PBIX file and publish the PBIX file to Power BI Service by clicking the Publish button in the upper right. Also put the PBIX file in the same folder as the original Excel file on OneDrive for Business.
2.PNG

3. Go to Power BI Service, go down to the Datasets and click  the ellipsis to the left of the Dataset you just published, in this dialog select “Schedule Refresh”.
3.png

Choose “Data Source Credentials” in the dialog that appears and then click on “Edit Credentials”, make sure that you select “oAuth2” in “Authentication Method” list, then sign in, enter your use organizational account and password.
4.jpg


After performing the above steps, make changes in your original Excel file which locates in OneDrive for business folder, then check if updates are uploaded to Power BI Service after a hour.

Thanks,
Lydia Zhang

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 did the first step. I have my excel workbook on a onedrive folder. However, I believe that if you follow the steps that you lay out, you will not be connecting to a onedrive file. Y9ou will need an on premise gateway connector. 

 

What I do is Get Data from web with the url from the Excel sheet on OneDrive. 

 

Then I save the Power BI Desktop File to OneDrive.

 

Then I go to Power BI Service and get data - From File - OneDrive For Business - Then choose my PBIX file. 

 

The way you have it set up is that you need to schedule refresh, but for OneDrive files, I shouldn't need to schedule refresh. I just need to make sure that we have the (OneDrive Refresh) on.

Hi @zrichardson

I have tested my steps and your steps, no gateway is required in both sceanrios. I follow the guide in this article to get web url of Excel file.

However, to make Power BI automatical refresh work, you would need to enter credential in Power BI Service to connect to the Excel source, which is shown in the above Step 3. After entering the credential , you don't need to set schedule refresh setting for the dataset.

Thanks,
Lydia Zhang

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.

If I do it the way you describe, these are my refresh options.

2016-12-30 09_39_34-Power BI.png

 

If I do it the way I describe, I get these options.

 

2016-12-30 09_41_22-Power BI.png

 

When I connect to the Power BI report through onedrive, I get the OneDrive Refresh option. When I publish to the service, I don't get that option.

 

Thanks

 

Hi @zrichardson,

Yes, when we connect to PBIX file using your steps, we can get onedrive refresh option. I test your scenario and get the following error message in refresh history. Do you get similar error message?
1.PNG 

Thanks,
Lydia Zhang

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.

Yes, I get this error.

 

2017-01-04 07_23_00-Power BI.png

Hi @zrichardson

In your sceanrio, when you add new measures, change column names, or edit visualizations in Power BI Desktop file, once you save, those changes will be updated in Power BI , usually within about an hour. When you make changes in the Excel file, you can only manually refresh the dataset by using Refresh now or set up a refresh schedule by using Schedule Refresh. For more details, please review this article.

However, if you want to make Onedrive hourly refresh work after making changes to Excel file, please directly connect to the Excel file located at Onedrive for Business from Power BI Service rather than from Power BI Desktop.

Thanks,
Lydia Zhang

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.

So you are telling me that if I want to have any measures, calculated columns, or custom visuals, I wouldn't be able to use the onedrive hourly refresh?

 

Thanks!

Hi @zrichardson,

In your current sceanrio, when you add any measures, calculated columns, or custom visuals in Power BI Desktop file, you are able to use the onedrive hourly refresh.

Thanks,
Lydia Zhang

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.

From what I am gathering, I can't have both.

 

If I want updated data every hour, I have to connect to excel through the service. 

 

If I want measures and columns, I need to connect to the power bi report through the service. 

 

Is there any way I can have both?

 

Thanks!

Hi @zrichardson,

In your sceanrio, it is not possible to sync changes from Power BI Desktop file and Excel file with OneDrive Hourly Refresh at the same time,but you can also set "Schedule Refresh" for your dataset expect OneDrive Refresh.

Thanks,
Lydia Zhang

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.

Hi,

 

I am a bit confused by these answers.  I have a similar issue:

 

1. Excel data file file stroed on onedrive for business

2. Powerbi Pro linked to excel file on Onedrive (using Gt Data > Web >url etc using OAuth2 credentials

3. Power BI Pro pbix stored on Onedrive.

4. Report published to Power BI service

5. Set up scheduled refresh (min update is 24 hours).  Also have Onedrive update (hourly) enabled

 

All good so far, in PowerBI service report has latest data

 

5. Add data to excel file on Onedrive.

6. In PowerBI Pro refresh report and see the latest data

7. In PowerBI Service - refesh but do not see new data

8. Wait an hour and refresh PowerBI service report - still not showing new data

9. I have not yet waited 24 hours to see if hte data is updated in PowerBi service

 

I had assumed that the hourly Onedrive refresh would update data and hence my report.

Is this the case please?  If not is there any way to update the PowerBi service report more frequently than 24 hours?

 

Thanks, Phillip

 

 

I need to use PowerBI Pro, for its additional capability

Yes, from what I have gathered, to get an hourly refresh of your OneDrive Excel data, you would have to be directly connected to it through the service. If you connect to the Power BI Desktop report, it will not be updated through the Power BI Hourly Update. If you make any changes to the Desktop Report, those will be picked up on the Hourly Update.

Then this is arbitrary close to being useless. We need to author reports in Desktop, and we need updated data.

That was my thoughts exactly. If you didn't need calculated columns or measures, you could connect the Power BI Service straight to your excel sheet and have hourly updates, but as soon as you want to make any more complex report or use custom visuals, that plan goes out the window.

Hi @zrichardson,

I will report this issue internally and post back once I get any updates.

Thanks,
Lydia Zhang

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.

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