This article explains, in expert-level detail, how to use Power Query M code to combine multiple files from a folder into a single table in Excel and Power BI, including the structure of the automatically generated queries, how to build your own robust M script, and how to optimize performance and maintenance for real-world reporting workflows.
1. Why combine files from a folder with Power Query M code
In many reporting scenarios, data is delivered as a series of files: one file per month, per region, or per business unit. Manually opening each workbook or CSV file and copying data into a master file is slow, error-prone, and impossible to scale. Power Query provides a folder connector that can scan all files in a directory and combine them into a single logical table, as long as the files share a compatible schema. This feature is available in Excel and Power BI and is designed specifically for recurring consolidation tasks.
When you use the folder connector and the built-in Combine Files experience, Power Query automatically analyzes the files, chooses an appropriate file format handler (text/CSV, Excel, JSON, etc.), and generates a set of helper queries and functions that transform each file and then append the results.
While the user interface is convenient, understanding and, when necessary, editing the underlying M code gives you far more control. You can filter files by extension, include or exclude subfolders, keep track of the originating file name, handle schema drift, and tune performance for large directory trees.
2. How the Folder connector and Combine Files engine work
2.1 High-level workflow
The typical workflow for combining files from a folder with Power Query is as follows.
- In Excel, use
Data > Get Data > From File > From Folder. In Power BI Desktop, useGet Data > Folder. - Select the folder containing the files you want to consolidate. A preview of all files and subfolders is displayed.
- Choose one of the Combine options (for example, Combine & Transform). Power Query uses the first file as a sample to detect the structure.
- In the Combine Files dialog, select the object to extract (for example, an Excel sheet or table, or a CSV content definition).
- Power Query generates helper queries and a function that transforms a single file, and a main query that:
- Calls the folder connector to list files.
- Filters the list of files as needed.
- Invokes the transformation function for each file.
- Expands and appends all transformed tables into one.
2.2 The queries Power Query generates
After using the Combine Files UI, you will typically see several queries created automatically:
- Sample File: A query that returns a single binary file used as a sample for building transformations.
- Transform File (function): An M function that takes one file (binary) as input and returns a transformed table.
- Transform Sample File: A helper query used while building the function, based on the sample file.
- Query for the Folder: The main query that lists all files in the folder, calls the transform function for each, and appends the results.
This structure is flexible: you can edit the function to add or change transformation logic, and the folder query will automatically apply the new steps to every file in the folder.
3. Core M code pattern to combine files from a folder
Although the automatically generated script can be verbose, the underlying pattern is straightforward. At its core, combining files from a folder consists of three steps:
- Use
Folder.Files(orFolder.Contents) to list all files. - Filter the files you actually want to combine.
- Transform and append the contents of those files into a single table.
3.1 Generic M template for combining CSV files
The following M code shows a generic pattern for combining all CSV files in a folder, keeping the source file name as a column.
let // 1. List all files in the folder (and subfolders) Source = Folder.Files("C:\Data\Sales"),
// 2. Keep only visible CSV files
FilteredFiles =
Table.SelectRows(
Source,
each [Extension] = ".csv"
and ([Attributes]?[Hidden]? <> true)
),
// 3. Read each CSV file into a table
AddDataTable =
Table.AddColumn(
FilteredFiles,
"Data",
each Csv.Document(
File.Contents([Folder Path] & [Name]),
[
Delimiter = ",",
Encoding = 65001,
QuoteStyle = QuoteStyle.Csv
]
)
),
// 4. Promote headers in each table
PromoteHeaders =
Table.TransformColumns(
AddDataTable,
{
"Data",
each Table.PromoteHeaders(_, [PromoteAllScalars = true])
}
),
// 5. Expand the Data column to create one combined table
ExpandedData =
Table.ExpandTableColumn(
PromoteHeaders,
"Data",
Table.ColumnNames(PromoteHeaders[Data]{0}),
Table.ColumnNames(PromoteHeaders[Data]{0})
),
// 6. Change data types as needed
TypedColumns =
Table.TransformColumnTypes(
ExpandedData,
{
{"Date", type date},
{"Region", type text},
{"Amount", type number}
}
)
in
TypedColumns3.2 Explanation of each step
| Step | M function | Purpose |
|---|---|---|
| Source | Folder.Files | Returns a table of all files in the folder and its subfolders, including columns such as Name, Extension, Folder Path, Attributes, and Content (the binary file). |
| FilteredFiles | Table.SelectRows | Filters the list so that only visible CSV files are included. This prevents combining temporary or hidden files. |
| AddDataTable | Table.AddColumn, Csv.Document | Adds a Data column that converts each file into a table based on CSV rules. |
| PromoteHeaders | Table.TransformColumns, Table.PromoteHeaders | Promotes the first row of each table to become column headers, ensuring consistent column names. |
| ExpandedData | Table.ExpandTableColumn | Expands all columns of the Data table into the main table, stacking rows from every file. |
| TypedColumns | Table.TransformColumnTypes | Applies correct data types for analysis and modeling. |
Note : The
Table.ColumnNames(PromoteHeaders[Data]{0}) pattern assumes that all files share the same schema. If a later file has extra or missing columns, Power Query will still combine the data but may introduce nulls or additional columns.4. Building a robust “Combine Files from Folder” pattern with a transform function
For more complex scenarios, especially when combining Excel workbooks or when the transformation steps are extensive, it is better to separate the logic into two parts:
- A function that transforms a single file.
- A folder query that invokes this function for each file and appends the results.
4.1 Function to transform a single CSV file
The following function accepts a binary file and returns a cleaned table.
(FileBinary as binary) as table => let Source = Csv.Document( FileBinary, [ Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv ] ), PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]), CleanTypes = Table.TransformColumnTypes( PromoteHeaders, { {"Date", type date}, {"Region", type text}, {"Amount", type number} } ) in CleanTypes4.2 Folder query that invokes the function
Now create a separate query that uses Folder.Files, filters files, and invokes the function for each file using the Content column.
let // Parameter or hard-coded path FolderPath = "C:\Data\Sales",
// List all files
Source = Folder.Files(FolderPath),
// Filter for CSV files only
FilteredFiles =
Table.SelectRows(
Source,
each [Extension] = ".csv"
),
// Add a column that calls the transform function
InvokeTransform =
Table.AddColumn(
FilteredFiles,
"Data",
each TransformCsvFile([Content])
),
// Expand the Data column
ExpandedData =
Table.ExpandTableColumn(
InvokeTransform,
"Data",
Table.ColumnNames(InvokeTransform[Data]{0}),
Table.ColumnNames(InvokeTransform[Data]{0})
),
// Optional: keep only relevant columns plus the source file name
ReorderedColumns =
Table.SelectColumns(
ExpandedData,
{
"Name", // source file name
"Date",
"Region",
"Amount"
}
)
in
ReorderedColumnsThis pattern mirrors the structure generated by the Combine Files UI, but you now have full control over the function’s logic and the folder filtering behavior. It is especially useful when you need to apply complex transformations to each file before combining them.
5. Advanced techniques for combining files from a folder
5.1 Parameterizing the folder path
Hard-coding paths such as C:\Data\Sales is brittle. A more robust pattern uses a query parameter or a dedicated query to store the folder path. In Excel and Power BI you can create a parameter via the interface and then reference it directly in your M code, or simply store it in a small table and read it with Power Query.
// Example: using a parameter named FolderPath let Source = Folder.Files(FolderPath), ... in ...This makes it easier to move the solution between environments (local, shared drive, SharePoint, etc.) without editing M code manually.
5.2 Filtering file lists efficiently
Folders often contain backup files, log files, or other data you do not want to include. Efficient filtering reduces processing time and prevents unwanted rows in the combined table. Common filters include:
- File extension: e.g., keep only
.csvor.xlsx. - File name pattern: e.g., include only files whose name contains
"Sales_"or matches a date pattern. - Subfolder path: use the
Folder Pathcolumn to include or exclude subdirectories. - Attributes: skip hidden or system files using the
Attributesrecord fields.
Note : Apply filters as early as possible in the query. Even though the folder connector does not provide query folding to an external source, early filtering still avoids unnecessary file reads and improves refresh times when there are many files.
5.3 Handling subfolders and file organization
Folder.Files returns all files from the specified folder and all its subfolders. This is convenient when data is separated by year or region in subdirectories. If you want to restrict the query to a single folder level, you can use Folder.Contents instead and filter out folders explicitly.
let Source = Folder.Contents("C:\Data\Sales"), OnlyFiles = Table.SelectRows(Source, each [Content] is binary), ... in ...5.4 Combining Excel workbooks
When combining Excel workbooks, Power Query typically uses Excel.Workbook(File.Contents(...)) inside the transform function. This function returns a table of all objects in the workbook (sheets, tables, named ranges) with metadata columns like Item and Kind. You then filter to the desired object, extract its [Data] field, and apply the same pattern as for CSV files.
(FileBinary as binary) as table => let Source = Excel.Workbook(FileBinary, null, true), SheetTable = Source{ [Item = "Data", Kind = "Sheet"] }[Data], PromoteHeaders = Table.PromoteHeaders(SheetTable, [PromoteAllScalars = true]) in PromoteHeadersThis approach allows you to combine the same sheet or table across many workbooks, even if the workbook names differ.
5.5 Dealing with schema drift
Real-world files are not always perfectly aligned. New columns may be added over time, headers may be renamed, or some files may be missing certain fields. You can mitigate schema drift by:
- Normalizing column names early (for example, using
Table.RenameColumnsto standardize header names). - Using
Table.SelectColumnswith the optional default parameter to ignore unexpected columns but keep expected ones. - Designing your transformations to be tolerant of nulls where older files lack new columns.
Note : When combining files with different schemas, Power Query will create a superset of all columns it encounters. Missing fields in a particular file simply appear as null in the combined table. Carefully check the combined headers after you add new file versions or columns.
5.6 Performance considerations
For large folders or complex transforms, performance is critical. Useful practices include:
- Filter early by extension, name pattern, or date to avoid processing irrelevant files.
- Perform heavy transformations inside the per-file function so that each file is processed independently and consistently.
- Remove intermediate columns and tables you no longer need to reduce memory usage.
- Consider splitting very large datasets into multiple queries or using incremental refresh strategies in Power BI where appropriate.
When configured correctly, Power Query can reliably consolidate many files into a single table that refreshes with a single command, eliminating manual consolidation work.
6. End-to-end example: monthly sales reports in a folder
This section illustrates a realistic scenario: combining monthly sales reports stored as CSV files in a folder structure such as:
C:\CompanyData\Sales\ 2024\ Sales_2024_01.csv Sales_2024_02.csv ... 2025\ Sales_2025_01.csv Sales_2025_02.csv ...6.1 Requirements
- Combine all CSV files under
C:\CompanyData\Sales(including subfolders). - Ensure columns
Date,Region,Product,Amountare present and correctly typed. - Include the source file name and year folder for traceability.
6.2 Transform function
(FileBinary as binary, FileName as text, FolderPath as text) as table => let // Read CSV Source = Csv.Document( FileBinary, [ Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv ] ),
// Promote first row to headers
PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
// Normalize column names (example)
RenamedColumns =
Table.RenameColumns(
PromoteHeaders,
{
{"SalesDate", "Date"},
{"RegionName", "Region"}
},
MissingField.Ignore
),
// Select only the columns we care about
SelectedColumns =
Table.SelectColumns(
RenamedColumns,
{"Date", "Region", "Product", "Amount"},
MissingField.UseNull
),
// Apply data types
TypedColumns =
Table.TransformColumnTypes(
SelectedColumns,
{
{"Date", type date},
{"Region", type text},
{"Product", type text},
{"Amount", type number}
}
),
// Add metadata columns for traceability
AddFileInfo =
Table.AddColumn(
Table.AddColumn(
TypedColumns,
"SourceFile",
each FileName,
type text
),
"SourceFolder",
each FolderPath,
type text
)
in
AddFileInfo6.3 Folder query that calls the function for every file
let // Root folder for all sales files RootFolder = "C:\CompanyData\Sales",
// Get all files in all subfolders
Source = Folder.Files(RootFolder),
// Keep only CSV files whose name starts with "Sales_"
FilteredFiles =
Table.SelectRows(
Source,
each [Extension] = ".csv"
and Text.StartsWith([Name], "Sales_")
),
// Invoke the transform function for each row
InvokeTransform =
Table.AddColumn(
FilteredFiles,
"Data",
each TransformSalesFile(
[Content],
[Name],
[Folder Path]
)
),
// Expand the Data table to produce the final combined table
ExpandedData =
Table.ExpandTableColumn(
InvokeTransform,
"Data",
Table.ColumnNames(InvokeTransform[Data]{0}),
Table.ColumnNames(InvokeTransform[Data]{0})
),
// Optional: remove technical columns you do not need
Cleaned =
Table.RemoveColumns(
ExpandedData,
{
"Content",
"Extension",
"Date accessed",
"Date modified",
"Date created",
"Attributes"
}
)
in
Cleaned6.4 Practical checklist
When implementing a folder-based combine pattern in production, use the following checklist.
| Checklist item | Why it matters |
|---|---|
| Confirm consistent column structure in source files. | Reduces errors and unexpected nulls when combining data. |
| Filter by extension and name as early as possible. | Improves refresh performance and avoids combining irrelevant files. |
| Keep source file and folder metadata columns. | Allows tracing rows back to their origin and debugging issues. |
| Store folder paths in parameters instead of hard-coding them. | Makes the solution easier to migrate between environments. |
| Review the combined table after adding new file types or columns. | Ensures schema drift does not break reports or measures. |
FAQ
Can I combine files from a folder in both Excel and Power BI using the same M code?
Yes. Power Query uses the same M language in Excel and Power BI. The folder connector and the Combine Files pattern work identically in both products, with only minor differences in how you access the queries and refresh settings. You can usually copy the M code from one environment to the other with minimal modifications.
How do I include only the latest file per month or year when combining from a folder?
You can add a step that groups files by a key derived from the file name (for example, year and month), then keep only the most recent file in each group. Use functions such as Table.Group and Table.MaxN or Table.Sort plus Table.FirstN within each group before invoking your transform function.
What if some files in the folder have a different column layout?
If some files have different headers or missing columns, Power Query will still combine them but will create a superset of columns. Rows from files that lack a particular column simply have null values in that column. To keep the model clean, you can normalize column names using Table.RenameColumns, select only the columns you need with Table.SelectColumns, and use the optional MissingField.UseNull parameter to handle missing fields gracefully.
Can I combine non-CSV files such as JSON, XML, or Excel workbooks from the same folder?
Yes. The folder connector is file-type agnostic. The key difference is the function you use to interpret the binary file. For Excel workbooks you typically use Excel.Workbook(File.Contents(...)), for JSON you use Json.Document, and for XML you use Xml.Tables. The surrounding pattern of listing files, filtering them, and expanding the results remains the same.
Is it better to rely on the Combine Files UI or write the M code by hand?
For simple scenarios, the Combine Files UI is usually sufficient and much faster to set up. For more advanced requirements such as complex transformations per file, dynamic filtering rules, or custom metadata tracking, you will often start with the automatically generated script and then refine it in the Advanced Editor. Understanding the underlying M patterns makes it easier to maintain and extend the query over time.
How does combining files from a folder affect refresh performance?
Power Query must read each file and apply your transformation logic at refresh time. Performance is mainly driven by the number and size of files, the speed of the storage system, and the complexity of your transformations. Filtering early, avoiding unnecessary columns, and keeping transformations streamlined inside the per-file function all contribute to faster refreshes. For very large datasets, consider partitioning folders or using incremental refresh in Power BI.