Power BI Service Refresh Failing for Dynamic SharePoint Web.Contents (Works in Desktop)

[Purva_2504]

Dear Microsoft Team,

I’m facing an issue with Power BI Service refresh when using dynamic SharePoint APIs via Web.Contents, while the same dataset refreshes successfully in Power BI Desktop.

---

🔹Scenario

• I am retrieving a Projects list from a central SharePoint site (ProjectsHub)
• Each project contains a URL to its own SharePoint site
• Based on this URL, I dynamically fetch the Tasks list from each project site using Web.Contents

  ---

  🔹Sample approach

  • TenantRoot = "https://mccnetmccneb.sharepoint.com",

    // --- Projects from ProjectsHub ---
    ProjectsHubRel = "/sites/ProjectsHub/_api/web/lists/getbytitle('Projects')/items",
    ProjectsRaw = Web.Contents(
    TenantRoot,
    [
    RelativePath = ProjectsHubRel,
    Query = [
    #"$select"="Title",
    #"$top"="10000",
    #"$format"="json"
    ],
    Headers = [
    Accept="application/json;odata=verbose"
    ]
    ]
    ),

    • ProjectsHubRel is derived from the project site URL
    • I loop through multiple project sites dynamically

    ---

    🔹What works

    ✅Refresh works perfectly in Power BI Desktop
    ✅Data is fetched correctly from all project sites

    ---

    🔹Issue in Power BI Service

    Refresh fails with:

    Data source error: The credentials provided for the Web source are invalid. (Source at https://mccnetmccneb.sharepoint.com/sites/ProjectsHub/_api/web/lists/getbytitle('Projects')/items.). The exception was raised by the IDbCommand interface.
    Cluster URI: WABI-US-NORTH-CENTRAL-J-PRIMARY-redirect.analysis.windows.net
    

    Even though:

    • Credentials are set to Organizational
    • Tenant root URL is consistent (https://mccnetmccneb.sharepoint.com)
    • No anonymous access is used

    ---

    🔹Additional details

    • I noticed that SharePoint sometimes returns XML (ATOM feed) instead of JSON

    • I am now trying to enforce JSON using:

      • #$format=json
      • Accept = application/json;odata=verbose

    • Still facing refresh issues in Service

    ---

    🔹My question

    Has anyone successfully implemented:

    ✅Dynamic SharePoint site calls using Web.Contents
    ✅Across multiple site URLs
    ✅With reliable refresh in Power BI Service

    ---

    🔹Looking for guidance on

    • Best practice for handling dynamic SharePoint site URLs
    • Whether Web.Contents is supported in this pattern in Service
    • Any recommended architecture (without fully staging data externally if possible)

    Thanks,
    Purva Shah

[v-saisrao-msft]

Hi @Purva_2504,

Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.

Thank you.

[Purva_2504]

Hi, Thank you for sharing solution, but issue is still persist, It is giving same error

[v-saisrao-msft · Accepted solution]

Hi @Purva_2504,

The fact that the query refreshes in Power BI Desktop but fails in the Service suggests the issue may be with how the Service handles dynamically built SharePoint paths, rather than with authentication. Even if TenantRoot and RelativePath are fixed, the Service may still treat the source as dynamic when RelativePath values are assembled from site URLs retrieved during query execution. As a troubleshooting step, hard-code a single project site path in RelativePath and test the refresh in the Service. If that works, it would indicate that dynamic path generation is contributing to the refresh failure.

Thank you.

[Purva_2504]

Ok

[v-saisrao-msft]

Hi @Purva_2504,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[trivedisunita]

Hi @Purva_2504 ,

Thanks for Your problem.

This is a  scenario where dynamic Web.Contents calls to multiple SharePoint site URLs behave differently between Power BI Desktop and the Power BI Service. The root cause is that the Service enforces stricter rules around dynamic data source paths and credential scoping.

Workarounds-

1. Use RelativePath with a Fixed Root

• Keep TenantRoot = "https://mccnetmccneb.sharepoint.com" constant.
• Pass site‑specific segments via RelativePath

This ensures the Service sees one consistent root domain

2. Parameterize Site URLs Safely-

• Store project site URLs in a parameter or table.
• Use RelativePath instead of concatenating full URLs.
• Avoid Web.Contents("https://mccnetmccneb.sharepoint.com/sites/ProjectA/...") directly — that creates multiple roots

3. Enable  Allow Data Source to Combine-

• In Power BI Service, go to Dataset Settings - Data Source Credentials - Privacy.
• Set privacy to Organizational and enable combining.
• This reduces blocking when multiple site paths are stitched together

4.  Force JSON Output

5. Consider Staging-
If dynamic expansion remains unstable, stage project/task lists into a single SharePoint list or Fabric Lakehouse.

Refresh against that consolidated source instead of looping through sites at runtime.

Microsoft has documented that dynamic Web.Contents across multiple site collections is not fully supported in Service.

The recommended architecture is to normalize site data into one source or use SharePoint Folder connector for consistent refresh.

If this reply helped, a kudos would mean a lot and mark it as a solutions .It encourages contributors and keeps the community strong.

Thank You

Sunita

[Purva_2504]

Hi Sunita,

Thank you for the detailed explanation and suggestions, they were very helpful.

I have implemented the changes you recommended:

• Used a fixed TenantRoot with RelativePath
• Avoided full dynamic URLs
• Set credentials and privacy level to Organizational
• Forced JSON output using $format=json and appropriate headers

In Power BI Desktop, everything works correctly. I am able to retrieve data dynamically from multiple project sites, and I also verified that the API responses are returning JSON and being parsed correctly (d → results) in Power Query.

However, in Power BI Service, I am still encountering the same error:

“The credentials provided for the Web source are invalid.”

Since I am relatively new to Power BI, I’m not sure if there might still be an issue in my query or approach.

From further testing, it seems the query is dynamically expanding across multiple SharePoint site URLs, which might still be treated as a dynamic data source during refresh.

If possible, could you please review or suggest improvements to the query pattern? Also, have you seen any alternative approaches to support a fully dynamic multi-site scenario without using a staging layer?

Thanks again for your guidance!

[v-saisrao-msft]

Hi @Purva_2504,

Thank you @trivedisunita, for your insights.
Since the query refreshes successfully in Power BI Desktop but fails in the Power BI Service, this could be due to how the Service manages dynamically generated SharePoint site paths, rather than an issue with credentials. Even though you have set a fixed tenant root using Web.Contents and RelativePath, if the site paths are constructed at runtime from the Projects list, the Service might still treat the query as a dynamic data source. You could try hard-coding a single project site path to see if the refresh works in the Service. If it does, this suggests that the dynamic URL generation may be causing the issue. Microsoft’s documentation on dynamic data sources and Web.Contents could provide further guidance.
Data refresh in Power BI - Power BI | Microsoft Learn

Web.Contents - PowerQuery M | Microsoft Learn

Thank you.