How can I import SharePoint list version data into Power BI

[Eric5605]

Hello, I have data on about 100 companies that is fed by a SharePoint list that has versions turned on. Over time, the SharePoint list is updated with new information. For example, as the number of employees changes, it gets updated. What I'd like to do is add a date slicer so a user can slide it back and time and the table showing statistics for those businesses would change to reflect what the data was on that past date. So ABC Company has 1,210 employees as of 9/28, and on 9/10 the employee count was changed from 1,288 to 1,210. I'd like to be able to slide the date slicer to the left and, for example, this record for ABC Company on the table would show1,210 employees until I slide back to 9/10 and before where it would then be 1,288. Basically getting a snapshot of all the data in the chart and view how it changes over time when you slide the date slider back.

I tied the modified filed to a calendar, but the slider is just filtering by all modified dates for any of the 100 company records. Then I tried adding the version records to the slicer and that just added a sublevel to select different version numbers. I'd like all 100 companies to continue to show on the table regardless of where the date slicer is moved to, but just have data change for each record as appropriate to show updates that were made over time.

I tried this post, but could not get it to work - https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=3f83a3d2-... 

Any help would be greatly appreciated!! Eric

[Eric5605]

Hello, I have about 110 different records/items in the SP list.  What should I enter for Item.ID if I want to track ther versioning on all of them?

[lbendlin]

You need to call the version function for each item separately. There is no call like " give me all versions of all items ".

The item ID is part of the data you get when you fetch the item list.

[lbendlin · Accepted solution]

You need to call the version function for each item separately. There is no call like " give me all versions of all items ".

The item ID is part of the data you get when you fetch the item list.

[Eric5605]

Thanks for the reply.  I couldn't get the Power Query task to operate from that post.  Likely I need more guidance walking through the steps as to how to customize the code and entries after I copy pasted the post's code into advanced editor.

[lbendlin]

1. Create the function to retrieve the versions.

GetVersions

(ListName as text, Location as text, ItemID as number) => let
        Source = Xml.Tables(Web.Contents(Text.Combine({
    Location,
    "/_api/web/Lists/getbytitle('",
    ListName ,
    "')/items(",
    Text.From(ItemID),
    ")/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"

2. Create the query that retrieves your sharepoint list items, and then for each item call the above function

let
    SP = "<your sharepoint site>",
    Source = SharePoint.Tables(SP, [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Title] = "<your list title>")),
    #"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 GetVersions([Title], SP, [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"

[lbendlin]

Message 16 here should get you going Getting SharePoint List items with full history ve... - Microsoft Power BI Community