Changing data source from local drive to Sharepoint file -PBI service refresh failed

[OZMckenzie]

Hello,

I have changed the data sources of one PBI report from excel files at local drive to files on sharepoint. I did the change by replacing the file path URL. I can do refresh on PBI desktop, however not on the PBI service.

I suppose it was the data credential, however there are only two options in the authentication method, both are not working as the error message shows "Failed to update data source credentials: The credentials provided for the Web source are invalid."

I cannot find the anthentication of "oauth2" that is said to be workable, I wonder if that's because my data source is a "web" instead of a "sharepoint"? If so, how do I change the data source to a sharepoint file without breaking the current report model?

Thanks a million in advance!

[v-diye-msft]

Hi @OZMckenzie 

Kindly refer to the blog below to work out the kinks:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power... 

[OZMckenzie]

Hi @amitchandak, @aj1973 , @v-diye-msft ,

Thank you all for great advice! 

@amitchandak, I did read the article before posting this inquiry, the mistake I made was copying the link in an incorrect way. I copied the link without opening the file in Excel apps. I can do PBI desktop refresh but unable to refresh the PBI service due to data credential.

By using the correct path, I am able to choose "OAuth2" to configure the data source for PBI service.

the URL I got by clicking "copy link" on sharepoint excel:
https://***.sharepoint.com/:x:/r/teams/****-***%20new%20data.xlsx?d=w0039779bb38845af9928d19b4eead043&csf=1&web=1&e=uqds4b

the URL I got by opening the file in excel apps and copy path in the Info page:
https://***.sharepoint.com/teams/****-***%20new%20data.xlsx?web=1

Thank you all again!

[aj1973]

Hi,

I had the same issue before when trying to change the source of the data. You need to open Power Query in power bi desktop and check if the source of the table is alos changed. Sometimes the change does not take effect until you changed in power query.

I am using a web source and local source for my report: 

https://app.powerbi.com/view?r=eyJrIjoiMjc2N2ZkZmYtMTNjYi00N2Q0LWE4MmUtYjhiY2E4NTQxZGM1IiwidCI6IjgzN...

[amitchandak · Accepted solution]

@OZMckenzie , refer if this can help

https://community.powerbi.com/t5/Desktop/Change-Data-Source-from-Local-Desktop-to-Sharepoint/m-p/505...