How to combine multiple files into one table with Power Query parameters
By the Analistable team · Updated · 5 min read
Create a text parameter (Home → Manage Parameters → New Parameter) holding the folder path, then edit the Source step of your combine query from Folder.Files("C:\Data\Sales") to Folder.Files(FolderPath). Add parameters for the file prefix or extension and use them in the filter step. Anyone can now point the query at another folder by changing one value instead of editing M code.
Part of our guide: How to merge data in Power BI, Power Query and Tableau
Why parameterise a combine
A folder combine hard-codes the path you browsed to. Send the workbook to a colleague, move the folder to SharePoint or reuse the query for another client, and the path is wrong. Parameters move those values to one visible place.
| Parameter | Type | Example value | Used in |
|---|---|---|---|
| FolderPath | Text | C:\Data\Sales\ | Folder.Files(FolderPath) |
| FilePrefix | Text | Sales_ | Text.StartsWith([Name], FilePrefix) |
| FileExtension | Text | .csv | Text.Lower([Extension]) = FileExtension |
| FromDate | Date | 2026-01-01 | Filter on Date modified or a date in the name |
Create the parameters
- In the Power Query editor choose Home → Manage Parameters → New Parameter.
- Name it FolderPath, set Type to Text, and enter the current path as Current Value. Leave Required ticked.
- Repeat for FilePrefix and FileExtension.
- Open your combine query, select the Source step and change the path in the formula bar to the parameter name.
let
Source = Folder.Files(FolderPath),
Filtered = Table.SelectRows(Source, each
Text.StartsWith([Name], FilePrefix)
and Text.Lower([Extension]) = Text.Lower(FileExtension)
and not Text.StartsWith([Name], "~$")),
Invoked = Table.AddColumn(Filtered, "Data", each #"Transform File"([Content])),
Kept = Table.SelectColumns(Invoked, {"Name", "Data"}),
Expanded = Table.ExpandTableColumn(Kept, "Data", {"Date", "Region", "Amount"})
in
ExpandedThe Combine Files helper already uses a parameter, Sample File, to drive Transform Sample File. That one picks which file the transform was designed on; it is separate from the parameters you add for the folder.
Let users change the path from a worksheet cell (Excel)
Manage Parameters means opening the editor. In Excel, a cell is friendlier. Put the path in a cell, give it a defined name such as FolderPathCell (Formulas → Define Name), and read it in a query:
// Query named FolderPath
let
Value = Excel.CurrentWorkbook(){[Name="FolderPathCell"]}[Content]{0}[Column1]
in
ValueOther queries refer to FolderPath exactly as before. Users type a new path in the cell and click Data → Refresh All.
Combining the workbook cell with a folder source can trigger a Formula.Firewall error about queries referencing other queries or data sources. It is caused by privacy-level checks. Setting both sources to the same privacy level, or restructuring so the cell value is read in the same query as the folder, usually resolves it. Check your organisation's policy before choosing to ignore privacy levels.
Parameters in Power BI
In Power BI Desktop, parameters appear under Transform data → Edit parameters, so report authors can change them without opening Power Query. After publishing, dataset owners can usually change parameter values in the semantic model's settings in the Power BI service. A local path such as C:\Data only refreshes in the service through an on-premises data gateway; for cloud refresh, point the parameter at a SharePoint or OneDrive location and use the SharePoint folder connector instead of Folder.Files.
The same parameters let you keep one template file (.pbit) that asks for the folder path when it is opened, which is a tidy way to hand a combine to another team.
Turn the whole combine into a function
To combine several folders with the same logic, for example one per region, make the query a function. Right-click the query and choose Create Function, or write it directly:
// fnCombineFolder
(path as text, prefix as text) as table =>
let
Source = Folder.Files(path),
Filtered = Table.SelectRows(Source, each Text.StartsWith([Name], prefix)),
Invoked = Table.AddColumn(Filtered, "Data", each #"Transform File"([Content])),
Expanded = Table.ExpandTableColumn(Invoked[[Name], [Data]], "Data", {"Date", "Region", "Amount"})
in
ExpandedThen a small table of folders drives it: add a custom column fnCombineFolder([Path], [Prefix]) and expand.
| Path | Prefix | Files | Rows |
|---|---|---|---|
| C:\Data\North\ | Sales_ | 12 | 14400 |
| C:\Data\South\ | Sales_ | 12 | 13250 |
| C:\Data\West\ | Sales_ | 9 | 9870 |
| Total | 33 | 37520 |
Checks after each change
- Count files per refresh with Group By on Name, so a wrong prefix that matches nothing does not pass unnoticed.
- A parameter typed as Text with a trailing backslash missing still works with Folder.Files, but string-built file paths elsewhere may not. Keep the convention consistent.
- If the combine starts failing after a path change, the new folder's files probably differ; see Combine Files errors.
For one-off analysis, Analistable skips the setup: pick the files, and they are read into DuckDB in your browser and queried in place, with nothing uploaded. Power Query parameters are the better choice for a combine that a team refreshes every month. See the Power BI, Power Query and Tableau guide for related techniques.
Document the parameters
A parameter is only useful if people know it exists. Give each a description in Manage Parameters, which appears as a tooltip, and in Excel put the path cell on a clearly labelled Settings sheet with a note on the expected format. A short table on that sheet helps the next person:
| Setting | Value | Notes |
|---|---|---|
| FolderPathCell | C:\Data\Sales\ | End with a backslash |
| FilePrefix | Sales_ | Case-sensitive |
| FileExtension | .csv | Include the dot |
Text.StartsWith is case-sensitive, so a prefix of “sales_” will not match “Sales_2026-01.csv”. If file names are inconsistent, compare lower-cased values on both sides, as the filter above does for the extension.
A final tip: when you hand the workbook to someone else, ask them to change the path and refresh while you watch. The first refresh on a new machine often prompts for folder credentials or privacy levels, and it is easier to answer those prompts together than to debug a vague error message by email later.
Frequently asked questions
- How do I change the folder path in Power Query without editing code?
- Create a Text parameter for the path and use it in Folder.Files. Change the parameter value in Manage Parameters, or read it from a named worksheet cell in Excel.
- Why do I get a Formula.Firewall error after adding a cell-based parameter?
- Power Query's privacy checks object to combining data from the workbook with data from a folder across separate queries. Align the privacy levels or read both in one query.
- Can I combine several folders with one query?
- Yes. Convert the combine into a function that takes a path, then invoke it on a table listing the folders and expand the results.