Forum Discussion

Eric5605's avatar
Eric5605
Helper I
3 years ago
Solved

How can I import SharePoint list version data into Power BI

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 e...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    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's avatar
Eric5605
Helper I
3 years ago

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's avatar
    lbendlin
    Super User
    3 years ago

    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"