relative link to file source in SharePoint Excel files

Question

I need to reference data between excel files stored in a SharePoint site; the data is in different files and different folders but in the same site.

Example: I need https://contoso.sharepoint.com/sites/sitename/Shared Documents/General/my_folder/[file1.xlsx]my_sheet!$A$1 to read the value of

https://contoso.sharepoint.com/sites/sitename/Shared Documents/General/my_other_folder/[file2.xlsx]my_other_sheet!$C$4

The connection needs to be with a relative path; I would like to reuse the same structure and query for different projects/sites like:

• https://contoso.sharepoint.com/sites/sitename_2/Shared Documents/General/my_folder/file1.xlsx
• https://contoso.sharepoint.com/sites/sitename_2/Shared Documents/General/my_other_folder/file2.xlsx

Like in this example, I'm able to do all the process with local files.

I'm in trouble with the on-line url path!

I've managed to find the path to the other files, from the root path.

A1 = cell('filename')
# A1 = "https://contoso.sharepoint.com/sites/sitename/Shared Documents/General/my_folder/[file1.xlsx]my_sheet"
A2 = LEFT(A1,FIND("[",A1)-1)
# A2 = "https://contoso.sharepoint.com/sites/sitename/Shared Documents/General/my_folder/"
A3 = TEXTJOIN("/", DROP(TEXTSPLIT("/", TRUE, A2),,1))
# A3 = "https://contoso.sharepoint.com/sites/sitename/Shared Documents/General/"
A4 = A3 & "my_other_folder/file2.xlsx"

I've named "ABS_PATH" the A4 cell.

In my PowerQuery advanced editor I've used

let
    FilePath = Excel.CurrentWorkbook(){[Name="ABS_PATH"]}[Content]{0}[Column1],
    TabName = Excel.CurrentWorkbook(){[Name="TAB_NAME"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    Sheet_fromMec = Source{[Item=TabName,Kind="Sheet"]}[Data]
in
    Sheet_fromMec

but the result is something like:

https:////contoso.sharepoint.com//sites//sitename//Shared Documents//General//my_other_folder//file2.xlsx

with a doubled "/" from the saved text in the string that returns a:

DataFormat.Error: The supplied file path must be a valid absolute path.

Does anyone know how to handle paths?

Answer 1

I'm using a config table loaded from Excel. Once in powerQuery the following function calls the table and gets the params of the file to lookup:

    (_table as table, FileId as text, Col as text) =>
        let
            Source = _table,
            Row = Table.SelectRows(Source, each ([File Id] = FileId)),
            Value=
                if Table.IsEmpty(Row)=true then null
                else Record.Field(Row{0}, Col)
        in
            Value

To retrieve a file (example with csv but you can adapt to get Excel sheet):

    let
      FileSite = GetFile(#"Sharepoint sources", "Sigma Structure", "Sharepoint site"),
      FileFolder = GetFile(#"Sharepoint sources", "Sigma Structure", "Folder"),
      FileName = GetFile(#"Sharepoint sources", "Sigma Structure", "Filename"),

      Source = SharePoint.Files(FileSite, [ApiVersion = 15]),
      #"Filtered rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], FileFolder)),
      #"Filtered Rows2" = Table.SelectRows(#"Filtered rows", each ([Name] = FileName)),
      OpenFile = #"Filtered Rows2"{[Name=#"Filtered Rows2"[Name]{0},#"Folder Path"=#"Filtered Rows2"[Folder Path]{0}]}[Content],

      #"Imported CSV" = Csv.Document(OpenFile,[Delimiter=";", Encoding=1252, QuoteStyle=QuoteStyle.None])
    in
      #"Imported CSV"