Forum Discussion

Nick's avatar
Nick
Frequent Visitor
10 years ago

Getting SharePoint List items with full history version?

Hi,   We do have a SharePoint list to manage Fleet inventory of asset: mainly their attribution (who own it) and their status (active, lost, broken, etc.). We did activated the SharePoint Version ...
Need Help
JensG's avatar
JensG
Advocate II
7 years ago

Hi

 

I like to share a function/query which will help to get the Version details via SharePoint API for a list item.

 

Function/Query:
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

3 parameters are required for the function:

VersionsRelevantSharePointLocation => https://<yourAddress>.sharepoint.com/sites/<yourSite>

VersionsRelevantSharePointListName => Your SharePoint List Name

VersionsRelevantItemID => SharePoint list item ID

when you invoke this Function to an SharePoint list you get the table with all related versions for that item.
This can be further expanded to get to the full set of columns from that list.

 

Hope this helps. 

 

Have Fun!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

please share how do we apply schedule refresh on this dynamic data source, 

  • mzzwtr's avatar
    mzzwtr
    Regular Visitor
    4 years ago

    Hi Anonymous,

     

    I suggest you to read these articles:

     

    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:

     

     

    Hope this may help you.

     

    Bye!