Getting SharePoint List items with full history version?

[Nick]

Hi,

We do have a SharePoint list to manage Fleet inventory of asset: mainly their attribution (who own it) and their status (active, lost, broken, etc.).

We did activated the SharePoint Version Control and I would like to use this information to run histroical report overtime around such assets status and ownership.

Right now, the only solution I found is to eitheir take a snapshot of the full list on a regular basis into another datasource.

Is there anyway to get such history/version data directly avoiding wruting such separate datasource?

Thansk in advance,

Nick

[tdaskalakis]

How do you expand this function to get the complete version history for all rows in a column?

[JensG]

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"

[devhack]

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.

[rantingdemon]

Great post, thank you very much!

[Anonymous]

Awsome! You made my day. Thanks a lot. 🙂

[Anonymous]

This one worked for me:

https://sharepoint.stackexchange.com/questions/190021/exporting-item-list-version-history

Short explaination:

The "&IncludeVersions=TRUE" gives all changes form all versions (form all fields)

Make a Sharepoint List which only contains the columns you need. (or split it up in several views and repeat this trick)

Open the XML in Excel en there you can create a PivotTable and if you include the version field in SharePoint view.

You can see the version number in the Pivot  Just sort on this version number and you will see the latest and all previous versions.

[madkins420]

There appears to be a 30 column limit on the &IncludeVersions=TRUE approach

[madkins420]

There appears to be a 30 column limit on the &IncludeVersions=TRUE approach

[llaumans]

Hi Nick,

did you ever figure out how to retrieve the history for items in a SharePoint list so you could run historical reports on your data? I would be very interested to learn how you managed to get this done!

Thanks in advance!

Kind Regards,

Lucas

[Nick]

Hi Lucas,

The current workaround I'm currently using since I don't have access to SharePoint DB/Framework is via R script:

Here I'm taking snapshot of Group, Status, Id, and ExtractDate data into a local CSV file, everytime I'm refreshing the Data Source.

Not fancy but it does the job for now.

Hope that will help.

Nick

[Anonymous]

This one worked for me:

https://sharepoint.stackexchange.com/questions/190021/exporting-item-list-version-history

Short explaination:

The "&IncludeVersions=TRUE" gives all changes form all versions (form all fields)

Make a Sharepoint List which only contains the columns you need. (or split it up in several views and repeat this trick)

Open the XML in Excel en there you can create a PivotTable and if you include the version field in SharePoint view.

You can see the version number in the Pivot  Just sort on this version number and you will see the latest and all previous versions.

[v-ljerr-msft]

@Nick

---

Right now, the only solution I found is to eitheir take a snapshot of the full list on a regular basis into another datasource.

Is there anyway to get such history/version data directly avoiding wruting such separate datasource?

---

You can export version history of SharePoint List Items to Microsoft Excel first. Then export that Excel table to your SharePoint List. So that the version history and  other data will be in the same datasource.

Regards

[Nick]

---

@v-ljerr-msft

Thanks but I can't execute PS scrript right now due to my company policies.

In addition I also wanted to avoid data multipliciation: maybe SharePoint list may not be good for what I'm looking for and I may ave to swicth to a real DB model.

[Greg_Deckler]

Well, you can get version history like so:

http://<server>/<site>/_layouts/versions.aspx?list={litsID}&ID=<itemID>

Here are some resources that might assist:

http://stackoverflow.com/questions/24423657/sharepoint-2013-get-splistitem-versions-via-rest

http://www.c-sharpcorner.com/UploadFile/anavijai/how-to-get-the-file-versions-in-sharepoint-2013-onl...

https://social.msdn.microsoft.com/Forums/en-US/79945c1d-1581-4441-bc13-14d119aa6a64/document-version...

[s-roberts]

Is there any update for this solution, considering this was a post from 2016? 

These solutions work well in PBI Desktop but when publishing to the service I'm unable to refresh the dataset.

[EBGAL]

Hi, @s-roberts  ,

Try the solution suggested by Chris Webb here.

As I understand it, you need to hide away part of the API call's string from Power Query and append it during the run, so the code would look like this:

Web.Contents("<your-webpage>",[RelativePath = "<continuation of the API call>"])

I was able to pull in full version history and be able to set auto-refresh on the Service. 

Thanks 

Evan

[NickTT]

I tweaked mine to use RelativePath and it still works fine from the Desktop but still fails to load with a refresh. I am using SharePoint online. Any tips?

[NickTT]

I found a work around. Load the data into a DataFlow first. In your Dataflow there is an Options button. In there click Project Options. Check the box "Allow combining data from multiple sources. This could expose sensitive or cofidential data to an unauthorized person.". With that set the query can now pull the data from both SharePoint and WebContent.

[Nick]

@Greg_Deckler

Thanks but it looks I can only extract one version of a specific item. However thanks for sharing URLs: a solution may be around.

[thakks]

Guys did you had any luck extracting all version from the sharepoint list by any chances..