Importing previous versions of Sharepoint Files into PowerBi

[omeallynile]

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? 

[lewis_patt]

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

[v-shex-msft]

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

[omeallynile]

Hi @v-shex-msft,

Thanks for responding,

The PiwerBi file references an excel file in SharePoint. That excel file gets emailed to us daily and put into SharePoint using Flow.

We have versioning enabled so when that file is copied in, it overwrites the existing file and saves it as a new version. This means that we can see previous versions at will.

The REST API can return version information for any given file, with a link to that file itself, which looks like 'https://tenant.sharepoint.com/_vti_history/[version]/[path]'.

That file downloads the version it corresponds with. No problem there. This has the historical data because it's an old version of the same file. I'm comfortable with that process. Let me know if I need to rephrase this part.

However, my issue is the way PowerBi handles that path. If I was downloading a regular file over rest, I would use 'https://tenant.sharepoint.com/_api/GetFileByServerRelativeUrl('[path]')/$value'. For previous versions, I can't use this. I have to use the absolute path as referenced above. PowerBi tries to help me by using the server relative portion of the above path - except you can't do that with _vti_history.

So, what I need is for it to basically use the URL I give it, and not try to help me. The only I seem to be able to get this to work is use http instead of https. I'm wondering if there is another way.

Hopefully that makes a little more sense!

[sethcc]

I know this is an old thread - did you find a solution?

[v-shex-msft]

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