OneDrive Excel to OneDrive pbix to Power BI Service

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?

Re: OneDrive Excel to OneDrive pbix to Power BI Service

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.

Re: OneDrive Excel to OneDrive pbix to Power BI Service

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.

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.

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

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.

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 ZhangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: OneDrive Excel to OneDrive pbix to Power BI Service

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.

Re: OneDrive Excel to OneDrive pbix to Power BI Service

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 ZhangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.