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!
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
- JensG6 years agoAdvocate II
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>)/versionshave fun
- ImkeF6 years agoCommunity Champion
Hi Anonymous
I had to add my SharePoint URL to this list of trusted sites in my Internet Options to overcome that error-message.