June 19, 2018July 22, 2019 by Marcus Rauhut

Using parameters to create dynamic file paths in Excel queries

Parameters are an incredibly powerful tool when constructing queries in Microsoft Excel. And if your source data changes frequently, parameters can come in extremely handy to change multiple file names/paths hurry.

For example, I had queries set up to pull data from about 30 spreadsheets from our finance department. Every month, they ran new reports with different file names in different folders based on that month’s name.

Rather than change the source for each query or manipulate the file names, you can accomplish a simple workaround using parameters in the query path.

Import the data from the external files and configure the query. If you are unfamiliar with this step, check out the tutorial from Microsoft: https://support.office.com/en-us/article/add-a-query-to-an-excel-worksheet-power-query-ca69e0f0-3db1-4493-900c-6279bef08df4
Once you’ve imported your external data source, click on the Data tab.
Pull down the Get Data menu and click on Launch Query Editor.
Click on Manager Parameters
Click New
Create parameters for parts of the file name that will be changing dynmically. In this example, the file folder and name both include the fiscal year and three-letter abbreviation for month. So the complete file path is C:\Documents\Reports\18 May\Services.xlsx
Now edit your query
Under Applied Steps, click the settings for your Source
Change source settings from Basic to Advanced
Use the form field below to construct the file path. Each part can either be text or a parameter. So, our file path would be:
Text – C:\Documents\Reports\
Parameter – Fiscal Year
Text – press the spacebar to insert blank space between the year and month
Parameter – Month
Text – \Services.xlsx
Repeat this for your other queries and then when you need to update multiple queries at once, simply change the parameter – in this case we would change the month to June when the June folder has been created.