How to Use Power Query to Automatically Merge Multiple CSV Files from a Folder

The Problem with Manual Data Consolidation

A common scenario in finance, sales, and logistics involves receiving periodic data dumps. For example, your software might export a new CSV file containing daily sales data every morning. If you need to analyze a whole month of data, you end up with 30 separate CSV files. The traditional method of opening each file, copying the data, and pasting it into a master workbook is tedious, error-prone, and entirely unsustainable.

Microsoft Excel includes a built-in data transformation tool called Power Query (labeled as “Get & Transform Data” on the ribbon) that solves this problem permanently. Power Query can connect directly to a Windows folder, automatically extract all the CSV files inside it, stack them on top of each other, and load the unified dataset into Excel. Best of all, when a new CSV file is added to the folder tomorrow, you only need to click “Refresh” to update your master sheet.

Step-by-Step: Merging CSV Files with Power Query

Step 1: Organize Your Files

Before you begin, ensure all the CSV files you want to merge are located in a single, dedicated folder on your computer (e.g., C:\SalesData\Q1\). Ensure that no other irrelevant files (like PDFs or images) are in this folder.

Crucial Requirement: For the merge to work flawlessly, all the CSV files must share the exact same column structure (e.g., Column A is Date, Column B is Region, Column C is Revenue).

Step 2: Connect Power Query to the Folder

1. Open a blank Microsoft Excel workbook.
2. Navigate to the Data tab on the ribbon.
3. Click on Get Data > From File > From Folder.
4. Click Browse… and navigate to the folder containing your CSV files (e.g., C:\SalesData\Q1\).
5. Click Open, then click OK.

Step 3: Combine and Transform

A preview window will appear listing the metadata of the files in the folder (File names, Extension, Date accessed). Do not load this metadata into Excel. Instead, we want the data inside those files.

1. At the bottom of the dialog box, click the Combine dropdown arrow.
2. Select Combine & Transform Data.

Excel will evaluate the files and present a “Combine Files” window. It uses the first file in the folder as a sample to determine the column headers and data types. Ensure the preview looks correct, and click OK.

Step 4: Clean the Data in the Power Query Editor

The Power Query Editor will now open. This is a separate window from standard Excel where you can manipulate data before it is loaded.

You will notice a new column on the far left called Source.Name. This column indicates which specific CSV file that particular row of data originated from. This is highly useful for auditing, but if you don’t need it, you can right-click the column header and select Remove.

At this stage, you can perform other automated cleaning tasks:

• Change Data Types: Click the icon next to a column header (like ‘ABC’ or ‘123’) to force a column to be treated strictly as Dates or Currency.
• Filter Rows: Use the dropdown arrows on the headers to remove blank rows or exclude specific regions.

Every action you take is recorded in the “Applied Steps” pane on the right. Power Query will execute these exact steps automatically every time you refresh the data.

Step 5: Load the Master Dataset into Excel

Once the data looks clean in the editor:

1. Click the Close & Load button in the top left corner of the Home ribbon.

Power Query will process all the CSV files in the folder and deposit a single, unified Excel table into your workbook.

The Magic of the “Refresh” Button

The true power of this method is its repeatability. Tomorrow, when you receive a new CSV file containing the latest daily data, do not open Power Query again. Do not open the master Excel file and paste the new data.

Simply drop the new CSV file into the C:\SalesData\Q1\ folder. Then, open your master Excel workbook, go to the Data tab, and click Refresh All. Power Query will reach back into the folder, discover the new file, extract its data, apply all your cleaning steps, and seamlessly append the new rows to the bottom of your table in seconds.

Conclusion

By utilizing Power Query to merge folders of CSV files, you transition from manual data entry to automated data pipelines. This not only saves immense amounts of time but also completely eliminates the risk of human error associated with repetitive copy-pasting, ensuring your Excel analysis is always built on accurate, up-to-date data.