Automate Monthly Reports With Power Query's Folder Connector
Stop opening five CSVs and pasting them together by hand. The Folder connector combines every file in a folder into one clean table automatically.
If part of your job is opening a folder full of monthly exports and pasting them into one master sheet, Power Query's Folder connector removes that step entirely. Set it up once, and every future month's files get picked up automatically on refresh.
Setting it up
- Data tab → Get Data → From File → From Folder
- Point it at the folder where your monthly files land (a OneDrive or SharePoint-synced folder works too)
- Click "Combine & Transform" rather than just "Combine" — this opens the Power Query Editor so you can shape the data before it loads
- Power Query picks one file as a template and applies the same transformation to every other file in the folder automatically
Try it yourself
Pick files from the folder — watch them combine automatically
| Region | Units |
|---|---|
| North | 120 |
| South | 95 |
2 combined rows from 1 file — this is exactly what the Folder connector does automatically every time new files land in the folder.
When file structures don't match
This is the single most common reason a Folder-connector query breaks. Two fixes, depending on the cause:
- If a source system occasionally renames a column, add a "Rename Columns" step early in the per-file transform, keyed by position rather than name where possible
- If some files genuinely have extra columns, use "Choose Columns" (not "Remove Columns") in the per-file transform — Choose Columns keeps working even if new, additional columns appear later; Remove Columns breaks if a column it expects to remove is missing
Filter early for speed
As the folder grows past a few dozen files, refresh time matters. The biggest lever is query folding: pushing filters and column selection as early as possible in the query, ideally before any custom transformations, so Power Query (or the source database) does the filtering instead of loading everything into memory first.
- Filter rows and remove unused columns as the very first steps, not the last
- Avoid adding custom columns before filtering — a calculated column early in the query can break folding for every step after it
- Check the query's step list: if "View Native Query" is greyed out after a step, folding has already stopped at that point
Keeping it reusable
Hard-coding the folder path makes the query break the moment it's shared with a colleague or moved to another machine. Instead, create a Power Query parameter for the folder path (Manage Parameters → New Parameter), and reference that parameter in the Folder connector's source step. Anyone opening the file just updates one parameter value instead of re-building the query.
Want the full worked example?
Automate Your Reports: Power Query for Beginners
A complete, practical ebook covering the Folder connector, query folding, modular queries, and a full monthly-report walkthrough.
Get the eBook — ₹199