Sharepoint Data Source file Invalid Credentials in Service

[rossnruthie]

I have created a power BI report that has two data sources (SQL On-premise via Gateway and a Sharepoint file in O365).

I can connect to and view both data sources without issue in desktop but after publishing the report to the services I am unable to refresh the report.  In the service my SQL source is fine and I update the Sharepoint file to use Oauth2 because of "Invalid Credentials" when trying to connect to the Sharepoint excel file.

In PBI Desktop my connection to our O365 Sharepoint site is set up like this:

let
    Source = SharePoint.Files("https://MyOrg.sharepoint.com/sites/ENTBIR", [ApiVersion = 15]),

and in the portal I sign in with my credentials which absolutely have permssion to view the data.  

After updating the credential for sharepoint the X next to it goes away which tells me all should be good.

But when I click on the dataset and select "refresh now" I get

Last refresh failed: Mon Jul 01 2019 15:16:04 GMT-0400 (Eastern Daylight Time) 
Invalid connection credentials.Hide details

Cluster URI:	WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID:	343657ec-ec8d-42cb-a87e-bf7093ed6557
Request ID:	050b9a42-68f1-d269-5ddd-46f84bbe2072
Time:	2019-07-01 19:16:04Z

Has anyone else had this issue?  What can i do to get this refresh working in the service?

[v-xicai]

Hi  @rossnruthie ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai · Accepted solution]

Hi @rossnruthie ,

In your scenario, to connect to Excel that is located under Documents of SharePoint online site, please use the URL below.

https://xxxxx.sharepoint.com/sites/<sitename>/Shared%20Documents/<Yourexcename>.xlsx

To connect to Excel that is located under a folder in SharePoint online Documents, please use the URL below.

https://xxxxx.sharepoint.com/sites/<sitename>/Shared%20Documents/<Foldername>/<Yourexcename>.xlsx

Then in the upper-right corner of the Power BI service, go to the gear icon  ->Manage gateways., tick on the option "Allow user's cloud data sources to refresh through this gateway cluster" like below, click "Apply". You can learn more: https://docs.microsoft.com/en-us/power-bi/service-gateway-mashup-on-premises-cloud.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.