Trouble scraping SharePoint Version History

[electrichead]

Hello,

I have a query that is intended to gather the version history from several files in a SharePoint folder.  Each file has several versions and I want to see who created the version and when to inform how regularly and recent our teams are updating their files.  The query works well it seems for most of the file versions (if it matters, the files are Excel).  However, I've noticed today that some file versions aren't being picked up by the query.  I can see the version history when I select the file in SharePoint, or open the file to look at the version history, and sometimes the most recent file is there, but not in my query.  In another case, I found several versions were missing.  But again, this doesn't seem to happen with all the files.

Refreshing the queries or refreshing the query preview does not load the missing file versions.  However, for the couple of instances that I've investigated more I was able to 'nudge' the query to see the missing version (or versions) by opening the Excel file myself (thus creating a version with autosave being on) and then refreshing the data preview.

Below is my query, if that helps.  I have replaced the root folder path with "my folder path".  I'm curious if anyone has an idea what is happening here or if there is a better way to query SharePoint to get this data more reliably.

Thanks for any suggestions!

let
FolderUrl = "/sites/Tracking/ExcelFiles",
Source = Json.Document(
Web.Contents(
"https://my folder path/_api/web/GetFolderByServerRelativeUrl('" & FolderUrl & "')/Files?$expand=ListItemAllFields/File/Versions/
CreatedBy",
[Headers=[Accept="application/json;odata=verbose"]]
)
),
DRecord = Source[d],
ResultsList = DRecord[results],
FilesTable = Table.FromRecords(ResultsList),
FilesExpanded = Table.SelectColumns(FilesTable,{ "Name", "ServerRelativeUrl","ListItemAllFields"}),
#"Expanded ListItemAllFields" = Table.ExpandRecordColumn(FilesExpanded, "ListItemAllFields", {"File"}, {"File"}),
#"Expanded File" = Table.ExpandRecordColumn(#"Expanded ListItemAllFields", "File", {"Versions"}, {"Versions"}),
#"Expanded Versions" = Table.ExpandRecordColumn(#"Expanded File", "Versions", {"results"}, {"results"}),
#"Expanded results" = Table.ExpandListColumn(#"Expanded Versions", "results"),
#"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"Created", "VersionLabel","CreatedBy"}, {"Created","VersionLabel", "CreatedBy"}),
#"Expanded CreatedBy" = Table.ExpandRecordColumn(#"Expanded results1", "CreatedBy", {"Title", "UserPrincipalName","LoginName"}, { "CreatedBy.Title", "CreatedBy.UserPrincipalName","CreatedBy.LoginName"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded CreatedBy", each ([Name] <> "zPLACEHOLDER - DO NOT REMOVE.xlsx")),
#"Changed Type4" = Table.TransformColumnTypes(#"Filtered Rows",{{"VersionLabel", type number}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type4", each ([Name] = "Resource Tracking -- ME-Ride On.xlsx")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"VersionLabel", Order.Descending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Created", type datetime}}),
#"Inserted Text Between Delimiters" = Table.AddColumn(#"Changed Type", "Text Between Delimiters", each Text.BetweenDelimiters([Name], "-- ", ".xlsx"), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Text Between Delimiters",{{"Text Between Delimiters", "Resource Group File Name Short"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"VersionLabel", Int64.Type}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type1", "VersionLabel", "VersionLabel - Copy"),
#"Changed Type2" = Table.TransformColumnTypes(#"Duplicated Column",{{"VersionLabel - Copy", type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type2",{{"VersionLabel - Copy", "VersionLabel - Text"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1","/sites/","https://my folder path/",Replacer.ReplaceText,{"ServerRelativeUrl"}),
#"Renamed Columns2" = Table.RenameColumns(#"Replaced Value",{{"ServerRelativeUrl", "Link to File"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns2", "Days Since Creation", each Duration.Days(Date.From(DateTime.LocalNow())-Date.From([Created]))),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Days Since Creation", Int64.Type}})
in
#"Changed Type3"

[v-achippa]

Hi @electrichead,

Thank you for reaching out to Microsoft Fabric Community.

This is expected behaviour with the SharePoint REST API and not an issue with power query or refresh. Refreshing power query does not change this because the incomplete data is coming directly from SharePoint.

If you need consistent and complete version history, versions must be queried per file, like for example:

GetFileByServerRelativeUrl('<file>')/Versions?$expand=CreatedBy

List the files first and then call the versions endpoint for each file. That is the only proper way for auditing version history.

Thanks and regards,

Anjan Kumar Chippa

[electrichead]

Thank you, although I don't fully understand why this is expected I can accept it for what it is (if you have a reference you can point me to so I understand better please do!  I'm assuming this is to save memory/processing power?  Not sure why else to limit data).

I've tried using the method you recommended to query the file instead of the file location, using the bit of code you supplied.  Unfortunately I now run into the issue of the published file not being able to be refreshed because of dynamic data sources.  Essentially, within the query I can build a list of the files I need the version history of and then extract it, but because this list is built within the query it is dynamic.  Do you have a suggestion on how to work around this?  I do need to run daily refreshes.

[vojtechsima]

Hey man, @electrichead ;

The issue with the dynamic source is an easy fix. Split your WebContents url into base part and RelativePath, like this: Web.Contents( "https://yourtenant.sharepoint.com", [RelativePath="theOtherPartsHere"]).

Check out my blog for a detailed way to get files the fastest way from SharePoint. Scroll to the last example.

https://www.vojtechsima.com/post/fix-slow-refreshes-faster-way-to-load-sharepoint-files-in-power-bi

[Murtaza_Ghafoor]

This is a SharePoint behavior rather than an issue with your query.
Sometimes version history isn’t fully exposed through the API until a file is opened or updated, which is why opening the Excel file makes the missing versions appear.
This is common behaviour.

Try this approach:

Use the List Items API (_api/web/lists/.../items?$expand=File/Versions) instead of querying the folder files endpoint, or ensure files are periodically accessed/updated to force SharePoint to refresh the version metadata.

[electrichead]

Thank you, any information you can point me to so I understand why this is common behaviour?  We do have the files automatically opened and saved on a regular basis (to ensure background queries within the files are refreshed).  I can see the version history of this when I view it in sharepoint or within the file, but the query still misses some of them.

I can give your suggestion a try, but am curious if I'll end up with a similar issue as with @v-achippa 's suggestion.  I need to put this report on a refresh schedule and that's where I had issues with that solution.  Any thoughts on if your suggestion will still allow a scheduled refresh?  I believe as long as I can point it to one location it should (vs. querying the files have many locations to reference).  And my other question is why is pulling the data from a list more accurate than pulling it from a folder?

[burakkaragoz · Accepted solution]

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

[electrichead]

Thanks @burakkaragoz, appreciate the complete explaination and code solution.  I tried this out and seems to be working, but (disappointingly?) in desktop the folder method is also returning a full list today.  I've set up a seperate report for myself, comparing the results, so I can check in on it.  Will be satifying to see when a difference show up.