Search code examples
excelsharepointpowerquerydataformat

Invalid absolute path error when parameterising Excel path on SharePoint site in Power Query

I have a valid, working, absolute path to an Excel file on a SharePoint site (e.g. "https://.../Filename.xlsx"), denoted by "FilePath".

I try to import some information from "FilePath" via Power Query. Using

let
    Source = Excel.Workbook(Web.Contents("FilePath"), null, true),

works fine. However, if I try to parameterise "FilePath" by placing it in the first row of an Excel table named Source_Files in column Source file found in the same Excel file where my Power Query resides (which I imported to PowerQuery), I always get

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

error, whether I try parameterising this way:

let
    Source = Excel.Workbook(File.Contents(Source_Files[Source file]{0}), null, true),

or this way:

let    
    FilePath = File.Contents(Source_Files[Source file]{0}),
    Source = Excel.Workbook(Web.Contents(FilePath), null, true),

How can I parameterise an Excel file on a SharePoint site in Power Query?

Solution

  • I think the syntax to replace "FilePath" would be:

    Text.From(Excel.CurrentWorkbook(){[Name="Source_Files"]}[Content]{0}[Source file])