|
// from: Power BI Community discussion: https://community.powerbi.com/t5/Desktop/Getting-SharePoint-List-items-with-full-history-version/m-p/776458/highlight/true#M374019 |
|
// note: consider posts below on use and trouble-shooting |
|
|
|
// Function fnGetVersionHistoryFromSharePointList |
|
let |
|
Source = (VersionsRelevantSharePointListName as text, VersionsRelevantSharePointLocation as text, VersionsRelevantItemID as number) => let |
|
Source = Xml.Tables(Web.Contents(Text.Combine({ |
|
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 |
|
|
|
// Sample Use in a Query: |
|
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 fnGetVersionHistoryFromSharePointList([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" |
@superbobstar , If I'm doing this it is failing to validate my Web dataset on Power BI Service. Please find the screen shot attached.
Please know that it is working fine in Power BI Desktop but not on Power BI Service.
Any help will be highly appreciated.