Power Query: Combine a Folder of Monthly Excel Files in One Click

Hello, tech enthusiasts! 👋

Every month, a new sales file arrives. You open it, copy the rows, and paste them under last month’s rows in a big master file. Then you do it again for the next file. And again. It is slow, and one wrong paste can break your whole report.

Today we will fix this with Power Query. Power Query is a tool inside Excel that collects and cleans data for you. Think of it like a recipe: you show Excel the steps one time, and it repeats them every time you ask.

At the end of this post, you will have one table that holds all your monthly files. Next month, you will add the new file to a folder and click one button. That is all.

What you need

  • Excel for Microsoft 365, Excel 2016 or newer (Power Query is built in)
  • A folder with your monthly files, for example C:\Reports\Sales
  • Files with the same layout: same column names, same order, same sheet name

For this example, our folder has sales_2023_01.xlsx, sales_2023_02.xlsx and sales_2023_03.xlsx. Each file has a sheet called Sales with the columns Date, Region, Product and Amount.

Step 1: Connect Excel to the folder

Open a new, empty workbook. This will be your master report. Then:

  1. Go to the Data tab.
  2. Click Get Data > From File > From Folder.
  3. Choose your folder (C:\Reports\Sales) and click Open.

Excel now shows a list of the files in the folder. You do not see the data yet, only the file names. This is normal.

Step 2: Combine and transform

At the bottom of the window, click Combine > Combine & Transform Data.

A new window opens. Excel asks you to pick a sample file. A sample file is like a model: Excel looks at it to learn the layout, then uses the same steps for all the other files. Choose the Sales sheet and click OK.

The Power Query Editor opens. You now see the rows from all three files in one table. Excel also adds a column called Source.Name. It shows which file each row came from. This is very useful, so keep it.

Step 3: Clean the data once

While you are in the editor, fix small problems one time:

  • Click the small icon next to the Date column name and choose Date.
  • Set Amount to Decimal Number the same way.
  • Right-click Source.Name and choose Rename to call it File.

Every click is saved as a step on the right side, under Applied Steps. You can click a step to see the data at that moment. You can also delete a step if you made a mistake.

Step 4: Skip files you do not want

Sometimes a folder has other files, like a PDF or a hidden temporary file. Temporary files start with ~$ and appear when someone has a file open. They can break the refresh.

To skip them, go back to the first query (the one with the file list). Click the Source step, then click the fx button next to the formula bar to add a new step. Type this formula:

= Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$"))

This keeps only .xlsx files and removes the temporary ones. Table.SelectRows is like the filter button in Excel, but written as a formula.

Step 5: Load the table into Excel

Click Home > Close & Load. Excel creates a new sheet with one clean table. Now you can build a PivotTable on it, add charts, or use formulas like SUMIFS.

Step 6: Next month, just refresh

When the August file arrives, save it into the same folder with the same layout. Then open your master workbook and click Data > Refresh All.

Power Query reads the folder again, finds the new file, and repeats all your steps. Your table now includes August. Your PivotTables update too, after one more Refresh All if needed. No copy, no paste. 🎉

Common problems

  • “The column ‘Amount’ of the table wasn’t found.” → One file has a different column name. Open it and fix the header so all files match.
  • Refresh shows an error about a file in use. → Someone has a file open, so a ~$ file exists. Add the filter from Step 4, or close the file.
  • Dates look wrong (month and day swapped). → Right-click the Date column, choose Change Type > Using Locale, and pick the locale of the source files.
  • You moved the folder. → Click the Source step in the first query and update the folder path.

Wrap-up

You built a small machine. It collects all your monthly files, cleans them, and puts them in one table. You set it up once, and now each month takes a few seconds.

Next, try adding a step that removes rows with an empty Amount, or group the data by Region with Transform > Group By. Power Query has many buttons, and each one you learn saves you more time. Happy automating!

fsaeed

, , ,