Sharepoint version history wild card power query code

Hi Anonymous

 

If you are happy with your current query's output for a single list item, I would suggest you do the following:

1. Convert your existing query to a function, that takes parameters SiteURL, ListName and ItemID (code below).
2. Create Power Query parameters for the Site URL and List Name.
3. Create a query using the SharePoint List connector, making use of these parameters.
4. Select at least the ItemID column from the list.
5. Apply the function from step 1 to each row.

Here is how you could write the function :

// fnSharePointListItemVersionHistory
(SiteURL as text, ListName as text, ItemID as number ) =>
let
  // Function parameters
  VersionsRelevantSharePointListName = ListName, 
  VersionsRelevantSharePointLocation = SiteURL, 
  VersionsRelevantItemID = ItemID, 
  RelativePathURL = Text.Combine(
    {
      "/_api/web/Lists/getbytitle('", 
      VersionsRelevantSharePointListName, 
      "')/items(", 
      Text.From(VersionsRelevantItemID), 
      ")/versions"
    }
  ), 
  Source = Xml.Tables(
    Web.Contents(VersionsRelevantSharePointLocation, [RelativePath = RelativePathURL])
  ), 
  entry = Source{0}[entry], 
  #"Removed Other Columns2" = Table.SelectColumns(entry, {"content"}), 
  #"Expanded content" = Table.ExpandTableColumn(
    #"Removed Other Columns2", 
    "content", 
    {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, 
    {"content"}
  ), 
  #"Expanded content1" = Table.ExpandTableColumn(
    #"Expanded content", 
    "content", 
    {"properties"}, 
    {"properties"}
  ), 
  #"Expanded properties" = Table.ExpandTableColumn(
    #"Expanded content1", 
    "properties", 
    {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, 
    {"properties"}
  ),
    #"Expanded properties1" = Table.ExpandTableColumn(#"Expanded properties", "properties", {"Title"}, {"properties.Title"})
in
  #"Expanded properties1"

 

And here is the final query containing versions per item in the list.

Note that you should create Power Query parameters Site URL and List Name first.

// ItemVersions
let
    Source = SharePoint.Tables( #"Site URL" , [Implementation="2.0", ViewMode="All"]),
    List = Source{[Title= #"List Name"]}[Items],
    #"Select Title and ID" = Table.SelectColumns(List,{"Title", "ID"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Select Title and ID", "VersionHistory", each fnSharePointListItemVersionHistory(#"Site URL", #"List Name", [ID])),
    #"Expanded VersionHistory" = Table.ExpandTableColumn(#"Invoked Custom Function", "VersionHistory", {"properties.Title"}, {"properties.Title"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded VersionHistory",{{"Title", type text}, {"properties.Title", type text}})
in
    #"Changed Type"

 

I have attached a PBIX which I tested using a dummy list created on my own SharePoint Online site.

 

Are you able to get something similar working?