Power BI Service error connecting to an excel database from Sharepoint

[chauh-dp]

Firstly, I will try to explain what I am trying to do:
I want to create a Power BI service that is accessible to my colleagues who do not have extensive experience with Power BI and do not have access to Power BI Desktop.
When I try to connect to my database using the semantic model in Power BI service, my data which is in SharePoint does not allow me to connect to it. Mentioned below are the errors that I get:

1. Unable to create connection using an organizational account, please check to see if the data source path is correct and that the data source supports using an organizational account. (Session ID: 22c68f85-d097-4105-8cc5-0d7179dccc91, Region: europe)

2. An exception occurred: DataFormat.Error: The input URL is invalid. Please provide a URL to the file path on SharePoint up to the file name only (with no query or fragment part). (Session ID: 22c68f85-d097-4105-8cc5-0d7179dccc91, Region: europe)

What should be the best way to connect a SharePoint Excel file with my Power BI service? Also, does this ":x:/r" in the SharePoint file path create issues in connecting?

[v-sshirivolu]

Hi @chauh-dp ,
Thanks for reaching out to the Microsoft fabric community forum.

1. Get the Correct File Path from SharePoint

Go to the document in SharePoint (via your browser).

Click the “...” (more options) - choose “Details” or “Copy link”.

You’ll get a link that looks like this:

https://yourtenant.sharepoint.com/:x:/r/sites/TeamSite/Shared Documents/Folder/File.xlsx?d=abc123&cs...

This is not valid for Power BI.

Convert it into a valid Web URL like this:

https://yourtenant.sharepoint.com/sites/TeamSite/Shared%20Documents/Folder/File.xlsx

 

Remove : x : / r   
Remove everything after .xlsx
Replace spaces with 20%.

2. Use Web Connector in Power BI Desktop (not Excel connector)

Open Power BI Desktop
Go to Home - Get Data - Web

Paste the corrected SharePoint URL:
https://yourtenant.sharepoint.com/sites/TeamSite/Shared%20Documents/Folder/File.xlsx

When prompted, authenticate using "Organizational Account" and sign in.

3. Publish the Report to Power BI Service

Go to the Power BI workspace

Click on the dataset (semantic model) -  Settings

Under Data Source Credentials, re-authenticate using Organizational Account

Enable Scheduled Refresh

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Sreeteja.
Community Support Team 

[Akash_Varuna]

Hi @chauh-dp Ensure you are using the correct file URL, formatted as https://<tenant>.sharepoint.com/sites/<SiteName>/Shared Documents/<FileName>.xlsx, without the ":x:/r" or query strings. Grant appropriate permissions to the file for your colleagues to access it. Use the "SharePoint Online List" connector in Power BI for authentication via an organizational account. Lastly, test the connection in Power BI Desktop and republish the report, ensuring the credentials are correctly set in the Power BI Service Data Source Settings.