Passing source folder path as parameter to query code in Power Query

During Power Query report creation you probably face the problem that you need to change data source file directory. It requires to go to the editor and manually, change static value of the source directory. Usually this approach will works fine, but it could be time consuming and error prone. So to overcome it, I externalize path to a file from query code into Excel table, which makes changing path directory more simple.

Step 1. Prepare standard query table

To begin create regular query table to access latest Excel file in a particular folder. I’ve covered it in previous blog post.

Step 2. Create table in working Excel file

Next we need to create table (in separate WorkSheet) that will contain folder paths where input files are stored.

  • Source — my custom name of the folder
  • Path — folder, where files are stored
  • Tab_name — name of the tab, which contains necessary data

Step 3. Create custom function to load folder path from config table

Within Query Editor, in the Queries list (left hand side) right click on Other Queries folder and select New Query -> Other Sources -> Blank Query.

let Parameter=(TableName as text,RowNumber as number) =>let     Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content],     value = Source{RowNumber-1}[Path]in     valuein Parameter
  • RowNumber — number of the row, which contains specific folder path.

Step 4. Create intermediate query table to load folder content

This is tricky part. In an ideal world we could use above function directly in the query. Unfortunately, when we replace source code path we get following error:

let
Source = Folder.Files(fParam("tableConfig", 1))
in
Source

Step 5. Replace static data source path with paths from dynamic table

Finally open query that was created in step 1 and in Advanced Editor replace following line:

Source = Folder.Files("D:\New folder (2)"),
Source = OrderList,

Java Software Developer, DevOps newbie, constant learner, podcast enthusiast.