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
