Forum Discussion
Getting SharePoint List items with full history version?
Hi
I like to share a function/query which will help to get the Version details via SharePoint API for a list item.
Function/Query:
let
Source = (VersionsRelevantSharePointListName as text, VersionsRelevantSharePointLocation as text, VersionsRelevantItemID as number) => let
Source = Xml.Tables(Web.Contents(Text.Combine({
VersionsRelevantSharePointLocation,
"/_api/web/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"
in
Source3 parameters are required for the function:
VersionsRelevantSharePointLocation => https://<yourAddress>.sharepoint.com/sites/<yourSite>
VersionsRelevantSharePointListName => Your SharePoint List Name
VersionsRelevantItemID => SharePoint list item ID
when you invoke this Function to an SharePoint list you get the table with all related versions for that item.
This can be further expanded to get to the full set of columns from that list.
Hope this helps.
Have Fun!
How do you expand this function to get the complete version history for all rows in a column?
Hi tdaskalakis,
here is an short example how to use the function:
1) Add an Query to your list where you want to see the version history:
let Source = SharePoint.Tables("https://<yourDomain>.sharepoint.com/sites/<YourSPSite>", [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Title] = "SampleList")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Items", "Title"}), #"Expanded Items" = Table.ExpandTableColumn(#"Removed Other Columns", "Items", {"Id"}, {"Items.Id"}) in #"Expanded Items"2) Invoke the Custom Function "GetVersionHistoryFromSharePointList" (from post above)
3.) after the invoke your table will have the column version which contains an table which contains a table per version of the item.
4) expand the Table.
5) now you have all possible items in a list, now you can further expand specific columns as you need.
Example code after those steps:let Source = SharePoint.Tables("<Your SP Site Link>", [ApiVersion = 15]), #"Filtered Rows" = Table.SelectRows(Source, each ([Title] = "SampleList")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Items", "Title"}), #"Expanded Items" = Table.ExpandTableColumn(#"Removed Other Columns", "Items", {"Id"}, {"Items.Id"}), #"Invoked Custom Function" = Table.AddColumn(#"Expanded Items", "Versions", each GetVersionHistoryFromSharePointList([Title], "<Your SharePoint Site>", [Items.Id])), #"Expanded Versions" = Table.ExpandTableColumn(#"Invoked Custom Function", "Versions", {"properties"}, {"Versions.properties"}), #"Expanded Versions.properties" = Table.ExpandTableColumn(#"Expanded Versions", "Versions.properties", {"IsCurrentVersion", "VersionId", "VersionLabel", "Title", "ExampleText"}, {"Versions.properties.IsCurrentVersion", "Versions.properties.VersionId", "Versions.properties.VersionLabel", "Versions.properties.Title", "Versions.properties.ExampleText"}) in #"Expanded Versions.properties"
Awsome! You made my day. Thanks a lot. 🙂
Great post, thank you very much!
This is great by the way, but for me it errors out when publish to Power BI service do to a query being called inside another query.
