Sharepoint version history wild card power query code

[byte007]

Hi all,

I'm wondering if anybody could help out on this code, in short this code will grab and (actually refresh once published) sharepoint version history.  Right now the code below uses a single record under the VersionsRelevantItemID but I would like to select all records not just one.

Does anyone please know how I can actually achieve this?  All the other different codes for sharepoint version history the refresh fails due to relative path.

Really appreciate any guide on this 🙂

let
  VersionsRelevantSharePointListName = "List Name", 
  VersionsRelevantSharePointLocation = "SharePoint Location", 
  VersionsRelevantItemID = "Wildcard Possbile", 
  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"

[OwenAuger · Accepted solution]

Hi @byte007

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?

[byte007]

Hi Owen,

Ah, that is absolutely brilliant - thank you for the example as now (like always) it makes sense what you did 🙂

The only stumbling block is of course the dreaded refresh problem as I'm not using a gateway which is correct for this but even after looking at the following which I think is now out of date I still cannot get refresh working as it fails with login credentials.  I believe it is normal as the url is not passed through correctly when testing connection.

Dynamic Web.Contents() and Power BI Refresh Errors – Data Inspirations

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

Thank you so much really appreciate your time and help for the fix

[OwenAuger]

You're welcome!

Ah yes, I hadn't tested refresh in the service. I am seeing the same issue you're describing.

I'll try to take another look and see if there's a solution for that!

[byte007]

Hi Owen,

I managed to fix this by with a bit of adjusting, here was the final code that worked with refresh.

// fnSharePointListItemVersionHistory

(ItemID as number ) =>

let

  // Function parameters
  VersionsRelevantSharePointListName = "Site Pages", 
  VersionsRelevantItemID = ItemID, 

  Source = Xml.Tables(
     Web.Contents(
    
    "https://xxxxx.sharepoint.com/teams/sitename/_api/web/", [RelativePath = "Lists/getbytitle('" & VersionsRelevantSharePointListName & "')/items(" & Text.From(VersionsRelevantItemID) & ")/versions"]

)
  ), 
        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"})

    in

        #"Expanded properties"

Many thanks again 🙂 

[OwenAuger]

That's great news @byte007 !

Glad you've got it working and thanks for posting the refresh-friendly code!

Wishing you all the best,

Owen