Forum Discussion

Csalinas144's avatar
Csalinas144
Helper II
4 years ago
Solved

Sharepoint List - Documenting Changes in Field Values

Hey Team,  Background:  I have groups of excel files on sharepoint which are being updated monthly.  I made 11 queries from those files (to reduce processing demand) Then I brought them into a ...
  • smpa01's avatar
    4 years ago

    Csalinas144 I know what REST URL you can use, that would give you the URL for each previous versions of the same file but I got stuck

    To elaborate, if your sharepoint details are following

    site_url = xyz.com/teams/Analytics
    Folder Name = /teams/Analytics/Shared%20Documents/DataMaster
    file_name =test.xlsx
    

    You can start with a REST URL like following and wrap that in Xml.Tables(Web.Contents())

    https://xyz.sharepoint.com/teams/Analytics/_api/web/GetFolderByServerRelativeUrl('/teams/Analytics/Shared%20Documents/DataMaster')/Files('test.xlsx')/versions?select=Url
    let
        Source = Xml.Tables(Web.Contents("https://xyz.sharepoint.com/teams/Analytics/_api/web/GetFolderByServerRelativeUrl('/teams/Analytics/Shared%20Documents/DataMaster')/Files('test.xlsx')/versions?select=Url")),
        entry = Source{0}[entry],
        #"Added Custom" = Table.AddColumn(entry, "Custom", each let x =[content],
        y = x{0}[#"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"]{0}[properties]{0}[#"http://schemas.microsoft.com/ado/2007/08/dataservices"]{0}[Url]
        in "https://xyz.sharepoint.com/teams/Analytics"&y)
    in
        #"Added Custom"
    

    It will bring you here

    Invoking Web.Contents on each of these URL does not return the excel and returns an error [Error Text -Specified value has invalid CRLF characters] while the same URL in browser returns that corresponding version of excel

     

    There is a previous question on this  https://community.powerbi.com/t5/Desktop/Importing-previous-versions-of-Sharepoint-Files-into-PowerB...

    and maybe omeallynile can shed some light on how to resolve that.