Spreadsheets

Power Query parameter: change your statement folder in one place

Create a text parameter in Power Query for your statement folder path, use it in the Folder.Files step, and change the folder in one place when files move.

Editorial illustration for Power Query parameter: change your statement folder in one place

A Power Query parameter is a named value that any query can use. Make a text parameter called FolderPath, put your statement folder in it, and write the Source step as Folder.Files(FolderPath). When the statements move to a new folder or drive, you change the parameter once and every query that uses it follows after a refresh.

Why not just type the path into the query?

The path then lives inside the Source step, and if several queries read the same folder (one per bank, say) each has its own copy. Moving the files means opening every query and editing every copy, and it is easy to miss one. A parameter turns that into a single edit.

How do I create the parameter?

  1. In the Power Query Editor, choose Home, Manage Parameters, New Parameter.
  2. Name: FolderPath. Type: Text. Suggested values: Any value.
  3. Current Value: the full folder path, for example C:\Statements\2025-26. Do not wrap the path in quotes.
  4. Click OK. The parameter appears in the Queries pane on the left.

Now open the query that reads your files. If you made it with Get Data, From Folder, its Source step contains a typed path. Open the Advanced Editor and replace the quoted text so the first lines read:

  • let
  • Source = Folder.Files(FolderPath),
  • Csvs = Table.SelectRows(Source, each [Extension] = ".csv"),

The rest of the query stays as it was. Folder.Files lists every file in that folder and its subfolders, so the Extension filter keeps only the CSV statements. Change ".csv" to ".xlsx" if that is what you download, and note the dot and the lowercase.

How do I change the folder later?

SituationWhat to editWhere
Statements moved to a new folderCurrent Value of FolderPathHome, Manage Parameters
New financial year folderSame parameter, new pathHome, Manage Parameters
Moved to a different drive or laptopSame parameter, then refreshHome, Manage Parameters

After editing, choose Close and Load and refresh the query. Say you keep each year in its own folder, such as Statements\2025-26 and Statements\2026-27. In April you change one value and all the reports are built from the new year, with no step edited.

Can I change it from a worksheet cell instead?

The parameter dialog is inside the editor, which is fine for you but awkward for anyone else who uses the file. Excel offers a different route: put the path in a cell, name it, and load that cell as a query.

  1. Type the folder path in cell B1 of a sheet called Settings, then select the cell and name it FolderCell in the Name Box.
  2. Select the cell, choose Data, From Table/Range, and let Excel create a table around it.
  3. Rename that query PathQuery, and keep the cell contents as the table's only value.
  4. In your statement query, set the first step to Source = Folder.Files(Excel.CurrentWorkbook(){[Name="PathQuery"]}[Content]{0}[Column1]).

The exact table name depends on how Excel names it, so check it in the Queries pane. This is more fiddly, but it lets you change the path on the sheet with no trip into the editor. A parameter is simpler if only you refresh the file.

What can go wrong after the change?

  • DataSource.Error saying the path was not found: check for a typo, and for a drive letter that does not exist on this computer.
  • A permissions or credentials prompt: a new path counts as a new data source. Choose Windows or Anonymous as appropriate and connect.
  • A privacy-level prompt when you combine the folder with another query: set the levels as the message asks, or see the separate article on that error.
  • Rows missing: the new folder may hold fewer files, or a different extension.

If statements are the only thing you pull into Excel and the monthly download-and-refresh routine is the chore, SpendixAI takes uploaded PDF, CSV or Excel statements directly and lets you review rows before saving, which removes the folder from the picture.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports