Power QueryIntermediate9 min read

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

  1. Data tab → Get Data → From File → From Folder
  2. Point it at the folder where your monthly files land (a OneDrive or SharePoint-synced folder works too)
  3. Click "Combine & Transform" rather than just "Combine" — this opens the Power Query Editor so you can shape the data before it loads
  4. Power Query picks one file as a template and applies the same transformation to every other file in the folder automatically
Prerequisite: every file in the folder needs the same column structure. If one month's export has an extra or renamed column, that file's transform step will fail on refresh.

Try it yourself

Pick files from the folder — watch them combine automatically

RegionUnits
North120
South95

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.

Once this is set up, a "monthly report" becomes: drop the new files in the folder, open the workbook, click Refresh All. That's the whole job.

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