Relative path with Web.Contents causing sharepoint authentication issues in Power BI

[putfrankfurter]

I'm experiencing an authentication issue when trying to load an Excel file from SharePoint using Power Query in Power BI Service. I'm using OAuth with an Organizational Account to authenticate against SharePoint.

When I pass the full file URL directly into Web.Contents, authentication with my organizational account works perfectly, like this:

let
  Source = Excel.Workbook(
    Web.Contents("https://company.sharepoint.com/sites/Finance/Reports/Q1_Report.xlsx"),
    null,
    true
  )
in
  Source

However, when I try to modularize the URL into a fixed base URL and relative path, sharepoint authentication fails.:

let
  FixedBaseURL = "https://company.sharepoint.com",
  RelativePath = "sites/Finance/Reports/Q1_Report.xlsx",
  Source = Excel.Workbook(Web.Contents(FixedBaseURL, [RelativePath = RelativePath]), null, true)
in
  Source

The reason I’m trying to structure it this way is because I need to pull in a list of Excel files from different SharePoint sites. I maintain this list in a separate Power BI table and loop through the URLs programmatically.

If I pass the URLs directly into Web.Contents like this:

Web.Contents(Record[URL])

…I get the following error in the Power BI Service: "This dataset includes a dynamic data source. Since data sources aren't refreshed in the Power BI Service, this dataset won't be refreshed."

Based on recommendations to solve this problem, parameterizing the URL using a base URL and relative path is supposed to resolve this “dynamic data source” error. However, when I try that method, I run into authentication failure with sharepoint.

My questions:

1. Is there a known limitation in Power BI’s OAuth flow when using Web.Contents with relative paths?
2. Are there any workarounds that allow dynamic file URLs (e.g., from a parameterized table) without breaking authentication or causing dynamic source errors?

Any ideas or recommended workarounds would be greatly appreciated.

[putfrankfurter · Accepted solution]

Unfortunately none of these suggestions fixed the issue. I was able to resolve this by setting up a power automate flow to copy all the excel files to a centralized sharepoint folder. 

[v-hjannapu]

Hi @putfrankfurter,
 I'm glad to hear that you found a solution and resolved the query. Thank you for sharing it here. Please mark your response as the accepted solution to help others in the community find it easily. Thank you for being a part of the Microsoft Community Forum

Regards,
Harshitha.

[v-hjannapu]

Hi @putfrankfurter ,

Thank you  for reaching out to the Microsoft fabric community forum.
Thank you @BhavinVyas3003, for your response regarding the issue.

first create a parameter for the base SharePoint URL like "https://company.sharepoint.com". After that, go to Power BI's data source settings and make sure this parameter is marked as “Always allow”. This tells Power BI that the URL is trusted and static.

Then, use the Web. Contents function in this proper way by passing the base URL as a parameter and adding the file path as a Relativepath. Also include any required headers if needed.

By doing this, Power BI will treat your SharePoint URL as a fixed source, not a dynamic one. This avoids the refresh error and allows your dataset to refresh properly in Power BI Service  and also keeps your OAuth authentication working without issues.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  
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,
Harshitha.
Community Support Team

[v-hjannapu]

Hi @putfrankfurter,

Just wanted to check if you had the opportunity to review the suggestion provided?

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

Thank You.

Harshitha.

Community Support Team.

[v-hjannapu]

Hi @putfrankfurter ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank You.

Harshitha.

Community Support Team.

[v-hjannapu]

Hi @putfrankfurter ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank You.

Harshitha.

Community Support Team.

[BhavinVyas3003]

Hi @putfrankfurter ,

Web.Contents with a RelativePath fails OAuth authentication in Power BI Service when used with SharePoint URLs. To avoid the "dynamic data source" error while preserving refresh capability, use Web.Contents with a static base URL and relative path, but also set the Query and Headers keys explicitly to ensure compatibility.

Also, define the base URL as a parameter with Always allow in the data source settings to make it static. Avoid passing full dynamic URLs directly—Power BI treats them as untrusted dynamic sources.

[GilbertQ]

Hi @putfrankfurter 

What if instead you created the urls dynamically into another table and then passed those full URLS into the contents as a source so that they're not going to be dynamic at the time that they're being called.