Trouble scraping SharePoint Version History

Hi  ,To fix your refresh issues and answer your question on "Why the List API," here is the technical breakdown and the code solution.

1. The "Dynamic Data Source" Fix

You are getting the refresh error because Power BI Service cannot authenticate a data source where the URL is built inside the query (e.g., combining a base URL with a file ID). The Service needs to know the Root URL statically before the query runs.

To fix this, you must use the RelativePath and Query options inside Web.Contents. This allows you to keep the main URL static (satisfying the gateway/refresh) while making the rest dynamic.

The Incorrect Way (Causes Dynamic Error): Web.Contents("https://site.com/api/files/" & FileID)

The Correct Way (Refreshable): Web.Contents("https://site.com", [RelativePath="api/files/" & FileID])

2. Why List API vs. Folder API?

You asked why the List API is more accurate.

• Folder API (GetFolderBy...): This is a file-system abstraction. When you ask for version history here, SharePoint often relies on a cached view of the file metadata to save performance. If that cache is stale (which happens often with deep metadata like versions), you get gaps until you "nudge" the file.

• List API (.../items): This queries the actual underlying SharePoint database table (All Documents are just items in a List). It bypasses the "folder view" abstraction and hits the transactional data directly. This is why Murtaza_Ghafoor recommended it.

  3. The Solution Code

  Here is how to combine the List API (for accuracy) with RelativePath (for refresh stability). This query gets all files and their versions in one go without the dynamic error.

  let
      // 1. Define your Static Base URL (The part you authenticate against)
      BaseUrl = "https://[YOUR_TENANT].sharepoint.com/sites/Tracking",
      
      // 2. Use RelativePath to hit the List API. 
      // We use the List Endpoint because it is the database source of truth.
      Source = Json.Document(Web.Contents(BaseUrl, [
          RelativePath = "_api/web/lists/getbytitle('Documents')/items",
          Query = [
              // We expand File and Versions immediately to avoid N+1 query loops
              #"$expand" = "File,File/Versions,File/Versions/CreatedBy",
              // Select only what you need to keep it fast
              #"$select" = "File/Name,File/ServerRelativeUrl"
          ],
          Headers = [Accept="application/json;odata=verbose"]
      ])),
  
      // 3. Standard JSON navigation from here
      d = Source[d],
      results = d[results],
      #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"File"}, {"File"}),
      
      // 4. Expand the Versions nested inside the File record
      #"Expanded File" = Table.ExpandRecordColumn(#"Expanded Column1", "File", {"Name", "ServerRelativeUrl", "Versions"}, {"FileName", "FileUrl", "Versions"}),
      #"Expanded Versions" = Table.ExpandRecordColumn(#"Expanded File", "Versions", {"results"}, {"VersionResults"}),
      #"Expanded VersionResults" = Table.ExpandListColumn(#"Expanded Versions", "VersionResults"),
      
      // 5. Expand Version Details
      #"Expanded Version Details" = Table.ExpandRecordColumn(#"Expanded VersionResults", "VersionResults", 
          {"VersionLabel", "Created", "CreatedBy"}, 
          {"VersionLabel", "VersionCreated", "CreatedByRecord"}),
          
      #"Expanded CreatedBy" = Table.ExpandRecordColumn(#"Expanded Version Details", "CreatedByRecord", {"Title", "Email"}, {"User", "UserEmail"})
  in
      #"Expanded CreatedBy"

  Key changes for your setup:

  1. Change BaseUrl to your site root.

  2. If your library is not named "Documents", change getbytitle('Documents') to your library name.

  3. This avoids the "Dynamic Data Source" error because BaseUrl is a static text string.

     Check out my blog for more on optimizing these calls if the list is very large!

     ---

     This response was assisted by AI for translation and formatting purposes.

electrichead