Parameter for Data Source when Data Source is a Folder

Caleb_RB · May 4, 2018

Hello,

I am trying to get a query that references a folder to change the file path with a parameter. I am wanting to do this so that it can be shared with several people and they can simple change the name in a cell and it will change the file path to their computer. However when I attempt to replace the file path with the parameter I get an error (attached below).

When doing this with anything other than a folder I can go to the source step and use the advanced option to add a parameter to the source, but I have not found the equivalent solution to this.

Any help with this would be greatly appreciated.

Caleb

horseyride · May 5, 2018

One way

I like to set up ability to do multiple parameters. So (a) create function Get Value

Code:

// name this function GetValue
(ParameterName as text) =>
let
ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName)),
Value=
if Table.IsEmpty(ParamRow)=true
then null
else Record.Field(ParamRow{0},"Value")
in
Value

(b) Create 2x2 table named "Parameters" in excel with column headers of "Parameter" and "Value" (c) In the first row under column headers, put "Path" and "c:\temp" (excluding quotes) as the parameter name and value
(d) Where you would normally have the path in any query, replace that path with GetValue("Path") so that

Code:

Source = Folder.Files("c:\temp"),

becomes

Code:

Source = Folder.Files(GetValue("Path")),

Any one can edit the parameter table to do a live change to the linked query paths

Parameter for Data Source when Data Source is a Folder

Caleb_RB

New member

Attachments

horseyride

New member