Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Connect to historical file versions on SharePoint

    Bronze Contributor
    Posted Jun 08, 2018 09:07 AM
    Is there a way to connect with PowerBI to different versions of the same file stored on SharePoint with revision history?

    I want to build a report for keeping track of changes to the data file somehow (For example comparing the latest version 202 with the previous version 201 or any other according to the needs), bit most of cases I find on internet deals with multiple files changing names monthly or so, but not overwritting the same file).

    revision history

    ------------------------------
    Domantas Gintauskas
    R&D Data Analyst
    GN Resound
    52829987
    ------------------------------


  • 2.  RE: Connect to historical file versions on SharePoint

    Bronze Contributor
    Posted Jun 11, 2018 01:42 AM
    It sounds like information about individual versions isn't exposed in the CSOM or REST APIs, so you're probably going to have to build your own web service wrapper, that queries SP and spits out the version based info you want, and then consume this with PowerBI.

    Retrieving SharePoint file version properties from the web API
    SharePoint Stack Exchange remove preview
    apple-touch-icon@2.png?v=24cd69cddbd9" width="200" height="200" />
    Retrieving SharePoint file version properties from the web API
    I'm trying to access the properties (column/field metadata) from old file versions in a document library, via SharePoint's web API. I can successfully query the library to get a list of files with their current versions using https://example.sharepoint.com/sites/[sitecollection]/_api/web/GetFolderByServerRelativeUrl('/sites/[sitecollection]/[libraryname]')/files, which exposes the URL for querying the file versions and file properties for each file in the library: .../_api/web/GetFileByServerRelativeUrl('/sites/[sitecollection]/[libraryname]/[filename]')/Versions .../_api/web/GetFileByServerRelativeUrl('/sites/[sitecollection]/[libraryname]/[filename]')/Properties Unfortunately I can't find a way of getting the properties for a particular version of a given file.
    View this on SharePoint Stack Exchange >



  • 3.  RE: Connect to historical file versions on SharePoint

    Posted Apr 28, 2020 11:45 PM

    Hi Domantas, 

    Did you ever figure this out?  I'm looking to do the same thing. 

    Thanks!
    Michelle



    ------------------------------
    Michelle Hersh
    Application Architect
    ------------------------------



  • 4.  RE: Connect to historical file versions on SharePoint

    Bronze Contributor
    Posted Apr 29, 2020 01:45 AM
    No, several people said it is not possible, so I guess it means it is not possible :)

    ------------------------------
    Domantas Gintauskas
    R&D Data Analyst
    GN Resound
    52829987
    ------------------------------



  • 5.  RE: Connect to historical file versions on SharePoint

    Posted 14 days ago
    I know this is an old topic, but after several frustrating hours looking for help online and finding only one or two threads with no solutions, I buckled down and came up with a solution to this.

    You can use the M function below to pull a list of all versions of a file in a SharePoint library with an associated URL that will actually work with Web.Contents.

    Usage (if you named the function Versions):
    Versions("https://<tenantname>.sharepoint.com", "/<sitename>", "/<path>/<filename>")


    Function:

    (SharePointTenant as text, SharePointSite as text, FilePath as text) as table =>
    
    let
        Base = Text.Combine({SharePointTenant,"/sites",SharePointSite,"/_api/Web/GetFileByServerRelativePath(decodedurl='/sites", SharePointSite, FilePath, "')/Versions"}),
        Source = Xml.Tables(Web.Contents(Base)){0}[entry],
        Cleaned = Table.RemoveColumns(Source,{"category", "link", "title", "updated", "author"}),
        Built = Table.TransformColumns(Cleaned, {{"content", (x) => let
            r = Record.FieldValues(Record.FieldValues(Record.FieldValues(x{0}){0}{0}){0}{0}){0}{0},
            ret = [
                    Created = DateTime.From(r[Created]{0}[#"Element:Text"]),
                    Url = Text.Combine({Base, "(", r[ID]{0}[#"Element:Text"], ")/$value"}),
                    VersionLabel = r[VersionLabel],
                    VersionID = r[ID]{0}[#"Element:Text"]
                ]
            in
                ret
            }}),
        Expanded = Table.ExpandRecordColumn(Built, "content", {"Created", "Url", "VersionLabel", "VersionID"}, {"Created", "URL", "VersionIndex", "VersionID"}),
        Extracted = Table.RenameColumns(Table.TransformColumns(Expanded, {{"id", each Text.End(_, 36), type text}}), {{"id", "GUID"}}),
        Typed = Table.TransformColumnTypes(Extracted,{{"GUID", type text}, {"URL", type text}, {"VersionIndex", Int64.Type}, {"VersionID", type text}, {"Created", type datetime}}),
        Organized = Table.ReorderColumns(Typed,{"VersionIndex", "Created", "GUID", "VersionID", "URL"})
    in
        Organized


    ------------------------------
    Lewis Patt
    Customer Support Administrator
    ------------------------------