Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hey all,
I've been trying to leverage Sharepoint revision history to compare a report we receive daily to historical data. I've written some stuff in REST and managed to isolate URLs for previous versions, and if I open that link in a browser, it indeed gets the old version.
However, when I pass that to Web.Contents(), it insists on 'helping' and tries to use GetByServerRelativeUrl('_vti_history/.../file.xlsx')/$value. I get that if was for a current file that would be fine, but for version history it is not.
For the time being, I've tricked it into working by using http instead of https, but does anyone have any idea as to how to get it to work correctly, or is this my only option?
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 solutuions, 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
HI @omeallynile ,
Can you please explain more about how to split two versions? Did you means historical data are based on current file and some filter?
Regards,
Xiaoxin Sheng
I know this is an old thread - did you find a solution?
Hi @omeallynile ,
How did you get data from previous file? Maybe you can try to use web connector with 'relativepath' option:
let Source = Web.Contents("https://tenant.sharepoint.com", [Headers=[#"key"=mykey], RelativePath="/_api/GetFileByServerRelativeUrl('[path]')/$value"]) in Source
Regards,
Xiaoxin Sheng