Power Query file path from cell value

Question

I have, in my excel workbook, a Table called ResultsTable, in that table there is a file path

C:\Users\XXXX\OneDrive - WORK\Digital Soil\Data\Results

I have Query that should get all excel files from the folder and transform the data into something usefull looking like this:

let
    Source = Folder.Files("ResultsTable"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Analyseresultater", each #"Transform File from Analyseresultater"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Analyseresultater"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Analyseresultater", Table.ColumnNames(#"Transform File from Analyseresultater"(#"Sample File"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table Column1",{"Key", "Attribute", "Value"})
in
    #"Removed Other Columns"

But I get the error

DataFormat.Error: The supplied folder path must be a valid absolute path. Details: ResultsTable

I hope someone can help me get through this error :)

EDIT: Added screenshot of how my sheet with tables are set up

What's the structure of Results Table ? Please edit your question to include this information. — Olly

Kosuke Sakai Kosuke Sakai · Accepted Answer · 2019-11-01T08:30:30

You can fix the code like,

let
    FilePath = Excel.CurrentWorkbook(){[Name="ResultsTable"]}[Content][Path to results]{0},
    Source = Folder.Files(FilePath),

In the original code, Folder.Files() was receiving the literal text "ResultsTable", not the cell value in ResultsTable. You need to first pick the cell value with Excel.CurrentWorkbook(), and then pass it to Folder.Files().

Power Query file path from cell value

1 Answers