cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nick
Frequent Visitor

Getting SharePoint List items with full history version?

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

27 REPLIES 27
mohandar
New Member

how can we get full version history of all items? I am trying to create report based on the no of days an item stayed in a particular status, any help will be appreciated.

JensG
Advocate I
Advocate I

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
    Source

3 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!

This is awesome! I just got a request if this was possible!

Anonymous
Not applicable

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

udobausc
Regular Visitor

@JensG cool that you posted a function/query. Unfortunately, it does not work for me. Any ideal how I can fix that without too much effort. The error says Expression.Error: The access to the ressourc is prohibites (translated from my version: "Der Zugriff auf die Ressource ist untersagt."). I checked that I am properly logged in and check the list that there are no permission restrictions. At least I cannot see any. I am assuming that the account I am logged into PowerBI must also have access the SharePoint list or is there anything else I need to consider?

Are you sure that the links in your function are still valid?

http://schemas.microsoft.com/ado/2007/08/dataservices/metadata
http://schemas.microsoft.com/ado/2007/08/dataservices

 Appreciate, if someone has a clue what is wrong with it?

 

Thank you so much

Hi @udobausc  

I had to add my SharePoint URL to this list of trusted sites in my Internet Options to overcome that error-message.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hi udobausc, 

 

sorry I don´t have seen this error on my end yet.

I´m logged in with my O365 Users in Power BI, same user who has access to the SP stuff.

Anyhow sometimes Power BI is promting to enter credentials where I can select differrent methods. But I always use the same user.

 

regarding the old links, it is when I expand some columns this is returned as field name and I have just kept it as it comes from the SP API 🙂


it might be worth to check if the underlying link is working in your browser when you replace the values in <>:

<VersionsRelevantSharePointLocation>/_api/web/Lists/getbytitle('<VersionsRelevantSharePointListName>')/items(<VersionsRelevantItemID>)/versions

 

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)

powerbi-1.png

 

 

powerbi-2.png

3.) after the invoke your table will have the column version which contains an table which contains a table per version of the item.

powerbi-3.png

4) expand the Table.

powerbi-4.png

5) now you have all possible items in a list, now you can further expand specific columns as you need.
powerbi-6.png


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"



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.

Great post, thank you very much!

Anonymous
Not applicable

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

Anonymous
Not applicable

This one worked for me:

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

Man Happy

 

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 Smiley Very Happy Just sort on this version number and you will see the latest and all previous versions.

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

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

llaumans
Frequent Visitor

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
Frequent Visitor

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
Not applicable

This one worked for me:

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

Man Happy

 

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 Smiley Very Happy Just sort on this version number and you will see the latest and all previous versions.

v-ljerr-msft
Microsoft
Microsoft

@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.

export.PNG

 

sharepointlist.PNG

Regards


@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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors