Dataset refresh from OneDrive Excel file set-up

[Anonymous]

I have a .pbix file made in Desktop Pro. The source of the data is excel files. I have uploaded the excel files on onedrive and made the required changes Following the threads:

https://community.powerbi.com/t5/Desktop/Changing-excel-file-data-source-location/m-p/206196#M90859

http://community.powerbi.com/t5/Desktop/Change-source-of-Excel-to-OneDrive-for-existing-reports/td-p...

I have also imported the dataset and report in service . However, the data is not refreshing and I do not see any options to enter my credentials. From what I understand, I do not need to schedule refreshes for the data in onedrive. Hence, fail to understand why PowerBI  is asking me to enter credentials.

Every other discussion in the forum or the microsoft documentation varies, because of the different versions of Power BI, I believe.  Can any one help with the latest update/version?

 

[v-juanli-msft]

Hi @Anonymous

“I have a .pbix file made in Desktop Pro. The source of the data is excel files. 

I have also imported the dataset and report in service . “

You connect excel files stored on OneDrive(for Business or Personal?) to Power BI Desktop via Web connector, then publish the pbix file to Power BI Service, right?

“From what I understand, I do not need to schedule refreshes for the data in onedrive. Hence, fail to understand why PowerBI  is asking me to enter credentials.”

Also, from your screenshot, you don’t install a gateway or configure datasoure to the gateway.

When you setup a refresh schedule, Power BI will connect directly to the data sources using connection information and credentials in the dataset to query for updated data, then load the updated data into the dataset. So you need to install a gateway and configure the credential for datasource.

Therefore, please follow the steps so you can refresh on Service site.

1.Connect files stored in OneDrive for Business to Power BI Desktop

Use OneDrive for Business links in Power BI Desktop

Note: To use a OneDrive for Business file as a data source in the Power BI service, with Service Refresh enabled for that file, make sure you select OAuth2 as the Authentication method when configuring your refresh settings. 

2.Download and install On-premises data gateway

3.Manage data source

4.Configuring scheduled refresh

Additionally, you may need know more about refresh for further use.

Understanding data refresh

Best Regards

Maggie

[Anonymous · Accepted solution]

Thanks Gilbert and Maggie,

The issue is resolved now. For some reason, OAuth2 was not populated as an option for 'Authentication Method' in DataSource Credentials when I had posted my issue. So to resolve, I redid the steps as follows:

1. I signed into my desktop and reentered the data source for all the excel files(onedrive) and made sure the permission for each file was set to 'Organizational Account' with Privacy level 'Organizational'. I also made sure that I was signed in as authorized user for each file.

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

2. I saved and upload this .pbix file to OneDrive for Business and imported in PBI Service using get data.

https://docs.microsoft.com/en-us/power-bi/service-connect-to-files-in-app-workspace-onedrive-for-bus...

3. This time, 'Edit Credentials' was enabled (earlier it was disabled) and I signed in for each and every excel file. And this time I was able to choose 'OAuth2' as Authentication method. No need to install/configure gateway. I Used OneDrive Refresh and I scheduled refreshes and looking at the refresh history, it worked great. 

https://5minutebi.com/2018/04/09/power-bi-using-and-refreshing-excel-datasources-with-onedrive/

https://medium.com/@Konstantinos_Ioannou/onedrive-powerbi-desktop-use-valid-paths-to-import-data-sto...

I have provided the links that were helpful. However, with the continuous updates from the microsoft team, some of the screenshots provided won't look exactly the same. 

[GilbertQ]

Hi there,

If you changed the connection to connect via a web URL then you will need a gateway to refresh the data.

How do you connect to your files in OneDrive?