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 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>)/versions

have fun 

Hi Anonymous  

I had to add my SharePoint URL to this list of trusted sites in my Internet Options to overcome that error-message.