How to get version history from SharePoint

[v-rzhou-msft]

Scenario:  

Every time we update our file in SharePoint, SharePoint will update the file version. We can find file versions in Version History.

But when we want to use SharePoint online list connector to get all version history, version columns will show empty.

Thought we can find Versions by expand File field, it will only show the latest versions.

Here I will show you how to get all Version History by Power Query and API.

Method:

The operation steps are as follows:

1.  We need to build a query to use SharePoint api in Power Query Editor.

Right Click in Queries —— Build a Blank Query —— Paste M code as below into Advanced Editor

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

2.  Then we connect to SharePoint by SharePoint Online List connector and go back to Source Step.

Due to we upload our files into Document field in SharePoint, all values we need is in this field. So we just need to keep Document field in Source.

Expand Item into Id. I have three files in Document, so max item id is 3.

Before we use Invoke Custom Function, we need to change the privacy level of two data sources to the same.   

Here the privacy level of two data source are organizational. Or our invoking step will show error. 

Then we will use Invoke Custom Function in the table we get from SharePoint.  

Invoke Custom Function is as below, select the query we built before in Function query. First box we need to enter the SharePoint list name, here I upload my file into Document. Second box we need to enter the link of your SharePoint site. The last box we select column name and select Item.ID column.

Click OK and expand “Version Query” by properties. Then Expand properties by “Version Label”. Result is as below. We get all version history from SharePoint List.

I hope this article can help you with the similar question.  

Author: Rico Zhou

Reviewer: Ula Huang, Kerry Wang

TestVersioncmment.pbix

[razmaster]

This is fantastic. Thank for putting the time top post this here

[Anonymous]

It's very nice, but How can I get the CheckInComments?

Regards

[WG_ELHT]

This is great many thanks.

Is there a way to get the date/time stamp from the appended comment? Either during this stage or prior?

Thanks.

[VishnuVardhan]

@v-rzhou-msft  I am able to pull version history from SharePoint in Power BI Desktop with above approach. However it is failing when I refresh data from Power BI Service. Any solutions?

error: This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

[Wendorio]

@VishnuVardhan A little change of the approach was needed, but this helped me:
https://community.powerbi.com/t5/Desktop/Power-BI-Service-with-dynamic-data-sources/td-p/1445175

In summary link for Web.Contents() needs to be split in two parts: a "hardcoded" base URL string and dynamic parameters

[Anonymous]

Hi @v-rzhou-msft,

I am new to Power Query, I am using your forumula below, I would assume I would need to input VersionsRelevantSharePointList Name with my list name and VersionsRelevantSharePointLocation with my list location and VersionsRelatevantItemID - what do I replace this with? I would like to pull out all the version history for all items listed in my SharePoint List not just for a particular item ID.

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

Do I need to update any of the below formula with my own column or row details?

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

[ctien]

HI Rico,

Thanks a lot for the great article.  I did set up the privacy level to  organizational. however  the column still show Error.  and detailed error message is Web.Contents failed to get contents from. (404): Not found

when i tried to copy and paste URL address to brower without versions it show xml on the page. however the full address with versions. it shows  this sml file does not appear to have any style info associated with it. the document tree is shown below .......  Cant find resource for the request versions 

Any ideas.  thanks a lot in advance 

[mike_honey]

Thanks a lot for this - the sample file with some variations was very helpful.

@Anonymous , @WG_ELHT - I found a way to get the comments from the previous versions. WRT this Answer from SE, you can call a Versions method. 
https://sharepoint.stackexchange.com/a/276677/13242

I was able to use that by cloning the VersionQuery function and mucking around a bit - have to change the API function called and get some different parameters, but I didn't have to touch the bottom half of the function.

I call that using a clone of the Documents query, then I used Merge Queries to blend that data with the Documents query in the sample file.

As noted in that Answer, a weird "feature" is that Versions doesn't return a row for the current version, only the history. You can dig a comments column out of the Versions.Properties data returned by the function described above, then muck around some more to get a tidy list.