OAuth2 reverting back to Anonymous - OneDrive Excel File

[granthworth]

Hello,

I recently updloaded a PBI desktop file to the service that uses 7 different Excel workbooks stored in OneDrive for Business as the data source. I connected all of them per the documentation ( https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links ) and am having some issues.

The first issue I noticed is that I did not get any option for the "hourly" refresh that is touted with OneDrive use in PBI. There was also no message or text anywhere stating this was the case. Upon investigating, I saw that the authentication method for all 7 files in the PBI service was set to "Anonymous" even though I had initially set them all to "OAuth2" per PBI documentation. When I changed them to OAuth2 again, I noticed that this setting does not save and they immediately go back to the Anonymous authentication.

Could this be the reason I am not seeing the hourly refresh and if so, how can I fix it? Thank you!

[v-yuezhe-msft]

@granthworth,

The issue is not related to the authentication. In your scenario, you would need to upload the Power BI Desktop file to OneDrive for Bussiness, then use "Gate Data->Files->OneDrive-Business" entry in Power BI Service to connect to the PBIX file, this way, you will see OneDrive Refresh(hourly refresh) option.

Regards,
Lydia

[granthworth]

@v-yuezhe-msft

Hi Lydia,

Thank you for your reply. I have since uploaded the .pbix file to OneDrive for Business and added it to another workspace in the Power BI service via "Get Data" in the PBI service. I now see the "Onedrive refresh" option which I have turned on. Now my question is...will this connection "pull-through" the .pbix file to retrieve the refreshed data from the Excel files also stored on OneDrive?

This is the current structure:

Excel files (the data changes here - these files contain the updated data) stored in OneDrive and imported into .pbix desktop file via Web link > .pbix file also stored in Onedrive > Imported the .pbix file into PBI service via "Get Data" with OneDrive refresh enabled

So technically, we are telling the PBI service to refresh the .pbix file, not the Excel files. Does this type of connection extend through to the data source Excel files and query for updated data even though we aren't directly refreshing them through the PBI service?

[v-yuezhe-msft]

@granthworth,

In your current scenario, 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 within about an hour. Data changes in Excel source file will not updated in Power BI Service unless you click Refresh now or set up a refresh schedule by using Schedule Refresh.

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

Regards,
Lydia

[granthworth]

How do I get the Excel data to refresh hourly? Per the documentation, this is possible (see the link below). What am I missing? Thank you for the help!!

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

[v-yuezhe-msft]

@granthworth,

You would need to directly connect to the Excel file from Power BI service.

Regards,
Lydia

[granthworth]

Thanks again Lydia. So, is there any way to build a report from a dataset with OneDrive Excel files in PBI Desktop while retaining the ability to refresh that dataset hourly via OneDrive refresh once it is published to the PBI service? If not, what was the purpose in this documentation which does not mention anything about limiting the features of the OneDrive connection? Frankly, I have a LOT of work in this dataset and report which I have built in PBI desktop. Is there a way to change the connection now in the PBI service to directly connect to the Excel files?

https://docs.microsoft.com/en-us/power-bi/desktop-use-onedrive-business-links

"Many people have Excel workbooks stored on their OneDrive for Business drive that would be great for use with Power BI Desktop. With Power BI Desktop, you can use online links for Excel files stored in OneDrive for Business to create reports and visuals. "

Another source stating the same procedure that I tried. No mention of degrading the refresh frequency here either.

http://community.powerbi.com/t5/Desktop/How-can-I-connect-to-an-excel-source-or-any-source-on-onedri...

[v-yuezhe-msft]

---

@granthworth wrote:

Thanks again Lydia. So, is there any way to build a report from a dataset with OneDrive Excel files in PBI Desktop while retaining the ability to refresh that dataset hourly via OneDrive refresh once it is published to the PBI service? 

---

@granthworth,

In this case, when you change data in the underlying Excel file, it is not possible to bring updated data to Power BI Service using OneDrive hourly refresh, you need to manually refresh or set schedule refresh, please check the statement in this official article.

When you store a Power BI Desktop file on OneDrive or SharePoint Online, any data you’ve loaded into your file’s model is imported into the dataset, and any reports you’ve created in the file are loaded into Reports in the Power BI serviced. When you make changes to your file on OneDrive or SharePoint Online, such as adding new measures, changing column names, or editing visualizations, once you save the file those changes will be updated in the Power BI service too, usually within about an hour.

When you import your Power BI Desktop file from OneDrive, or SharePoint Online, data, along with other information about the model is loaded into a dataset in Power BI. In the Power BI service, not Power BI Desktop, you want to refresh data in the dataset because that is what your reports, in the Power BI service, are based on. Because the data sources are external, you can manually refresh the dataset by using Refresh now or you can setup a refresh schedule by using Schedule Refresh.

Regards,
Lydia

[granthworth]

Hi Lydia,

Anything is possible. Microsoft just hasn't realized this is a problem yet. There is no excuse for only being able to build a report in the PBI service. Users should be able to use their OneDrive data files to build their calculated columns, measures, etc in Desktop and then enable OneDrive refresh once they are published to the service. Nothing else makes any sense.

I started an "Idea" for this topic here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33341050-enable-data-source-onedr...

For anyone else frustrated by this problem, I received this email in response to my Microsoft Service Request.

"We apologize for the inconvenience caused due to the product limitation or product not working as per your expectation. I totally agree with you and all of your concerns and they are completely valid. Please consider my apologies.

Our Product team pushes improvements and features on weekly basis. These improvements are planned well in advance. But there are some of the improvements which will have lot of dependencies due to which it takes long time and sometimes it becomes extremely hard for us to define an ETA even though we have a road map of improving the feature.

Your interest and efforts you take to use our product is highly valuable and I will surely pass on your community URL to be acknowledged by our product team and subjecting it to the list of future enhancements. So that we will hopefully rectify such limitations in Power BI."