Forum Discussion
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...
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.
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.
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"