Getting SharePoint List items with full history version?

Hi Anonymous,

I suggest you to read these articles:

• Setting a scheduled refresh on a Dynamic Data Source in Power BI 
• Web.Contents(), M Functions And Dataset Refresh Errors In Power BI 
• Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code 

To set a scheduled refresh using dynamic data source, I've edit the function of JensG in this way:

let
    Source = (VersionsRelevantSharePointListName as text, VersionsRelevantSharePointLocation as text, VersionsRelevantItemID as number) => let
        Source = Xml.Tables(Web.Contents(
    "https://YourAddress.sharepoint.com/sites/",
    [RelativePath = 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:

1. VersionsRelevantSharePointLocation => The subdirectory of your SharePoint site (is what come after ...sites/)
2. VersionsRelevantSharePointListName => Your SharePoint List Name
3. VersionsRelevantItemID => SharePoint list item ID

Then, once you have published your report into Power BI service, I raccomend to read these articles:

• [Unable to combine data] accessing data sources that have privacy levels
• Information is needed in order to combine data

Hope this may help you.

Bye!

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)

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

4) expand the Table.

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



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"

Hi, s-roberts  ,

Try the solution suggested by Chris Webb here.

As I understand it, you need to hide away part of the API call's string from Power Query and append it during the run, so the code would look like this:

Web.Contents("<your-webpage>",[RelativePath = "<continuation of the API call>"])

I was able to pull in full version history and be able to set auto-refresh on the Service. 

Guys did you had any luck extracting all version from the sharepoint list by any chances..

This one worked for me:

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

:manhappy:

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