cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Dataset refresh from OneDrive Excel file set-up

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?

 

 Credentials error.pngCredentials error2.pngRefresh error.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Dataset refresh from OneDrive Excel file set-up

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. 

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Dataset refresh from OneDrive Excel file set-up

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?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Highlighted
Community Support
Community Support

Re: Dataset refresh from OneDrive Excel file set-up

Hi @sgoel

“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?

3.png

“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

Highlighted
Frequent Visitor

Re: Dataset refresh from OneDrive Excel file set-up

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. 

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors