Easy Dynamic File Sources in PowerQuery

Break Free of Static Data Sources

Two common data sources in PowerQuery are getting data from a single file like one Excel workbook or getting all files in a folder. When you initially set up the query, it hard-codes the file or folder path into the query itself, essentially burying it unless you’re one of the elite few proficient in M language or navigating the Advanced Editor.

But what if another person wants to be able to refresh the data on their computer? Or what if your source file name or folder changes? In that case you need a dynamic file source directly inside the query. Essentially, the query will know to grab the file(s) from whatever location is written in Excel. Once it’s set up then updating your query’s data source becomes as easy as changing the value in a cell in your spreadsheet – no query editing or coding required.

A lot of tips online hinge on creating a dedicated file path table or using a custom parameter or invoking a custom function in PowerQuery. But that can look clunky and out of place in your spreadsheet. The reality is that you really only need a named range pointing to a cell. So let’s get started.

PowerQuery Dynamic File Source

1. Create your query in PowerQuery as normal (also known as Get & Transform Data)
2. Designate a cell in your spreadsheet where you’ll store the data source’s file path and file name.
3. Create a named range pointing to that cell. In this example we’re using rngFilePath
4. Click the Advanced Editor button in the Home Screen to bring up this line in the formula window:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Your Folder\SampleData.xlsx"), null, true),

5. Change it to this:

let
    Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="rngFilePath"]}[Content]{0}[Column1]), null, true),

6. Click Close & Load.

PowerQuery Dynamic Folder Source

1. Create your query in PowerQuery as normal (also know as Get & Transform Data)
2. Designate a cell in your spreadsheet where you’ll store the data source’s folder path.
3. Create a named range pointing to that cell. In this example we’re using rngFolderPath
4. Click the Advanced Editor button in the Home Screen to bring up this line in the formula window:

let
    Source = Folder.Files("C:\Users\Your Folder\Articles"),

5. Change it to this:

let
    Source = Folder.Files(Excel.CurrentWorkbook(){[Name="rngFolderPath"]}[Content]{0}[Column1]),

6. Click Close & Load.

Try It Out Yourself

Now you can update your data source file names or folder locations directly in Excel!

One note is that if your queries are dependent on an auto-created sample workbook you may have to make the update there instead. You’ll know that’s the case if you see a folder called Helper Queries with an odd assortment of sample files, parameters and queries.

Download this example workbook and try it out for yourself. It consists of the PowerQuery workbook and a dummy data Excel file.