cancel
Showing results for 
Search instead for 
Did you mean: 
RicoZhou

How to get version history from SharePoint

Scenario:  

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

1.png

 

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

 

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

 

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

 

5.png

 

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

 

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.

7.png

 

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

 

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

 

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

 

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

 

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.12.png

 

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.13.png

 

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

 

 

Author: Rico Zhou

Reviewer: Ula Huang, Kerry Wang

Comments

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

Polls
What is your favorite Power BI feature released this month?