Sharepoint List - Documenting Changes in Field Values

[Csalinas144]

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 

[smpa01 · Accepted solution]

@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.

[edhans]

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]

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]

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.

[mahoneypat]

Not an expert but know enough to think this is not a road you want to go down.  First, if these are xls files, you wouldn't be able to easily pull the data out of each file (easiest if it is formatted as a table in an xlsx file).  If you could do that, you likely wouldn't be able to store just the changes, so you'd be storing each file over again when it is modified (the thing that would trigger your flow).

If possible, I would consider an alternate data source (Dataverse, database, SharePoint list, etc.).

Pat

[AlexisOlson]

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.

[Csalinas144]

What Types of coding, tools, ect. would I need to make this happen? 

@AlexisOlson