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 power desk top for additional transformations/measures/and visualizations. 

If a Team Member changes/updates a value in one of the source files (.xls) then the change is currently not recorded and saved. 


I want to be able to extract the older versions as the team member changes the cell values to track a progress. 

Is there a way to do this?? 

Thank You, 


 

Chris

Power Desk Top Newbie
amitchandak Fowmy Greg_Deckler VahidDM edhans KNP MFelix smpa01 Burningsuit 

Need Help
Tutorial Requests
  • smpa01's avatar
    smpa01
    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.

     

7 Replies

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    The only reliable way is to archive off old versions and track those timestamps on the file. The tracking is simple, but keeping archived versions of Excel, not so much. That would be a bit of a pain.

    • Csalinas144's avatar
      Csalinas144
      Helper II
      4 years ago

      Hmmm. I am willing to learn new things to make it happen. 

      I am currently messing with a Flow through power automate. I am just not sure where to start. And where I am going on this one. 

      Could you help me out? edhans 

      • edhans's avatar
        edhans
        Community Champion
        4 years ago

        No, I am not a Power Automate expert. mahoneypat is. He could chime in, or you could go to the Power Automate forum and check there.

  • smpa01's avatar
    smpa01
    Community Champion
    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.

     

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    If you change the underlying data, you need to refresh that query to update Power BI. After refreshing, Power BI no longer has any idea what the previous data was.

     

    Power BI is not an appropriate tool for storing versions of data. You need some sort of database or similar backend to handle that sort of thing.