How to Merge Files in Power BI

Wrangling multiple sales reports in different Excel files every month is a tedious, all-too-common task. Merging data from these separate files into one master dataset is essential for proper analysis, but the manual copy-and-paste routine is slow and prone to errors. This guide will walk you through Power BI's powerful and automated way to combine multiple files from a single folder, saving you hours of work.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Combine Files in Power BI?

Before jumping into the steps, it's helpful to understand why moving this process into Power BI is so effective. Manually combining CSV or Excel files is not just a drain on your time, it's an unreliable way to manage data. Every time you copy and paste, you risk introducing an error. By connecting Power BI directly to a folder, you create an automated workflow.

• It's Efficient: After the initial setup, you can add a new file (like February's sales report) to the folder, hit "Refresh" in Power BI, and your entire report instantly updates with the new data. No more repetitive work.
• It's Scalable: This method works just as well for three files as it does for three hundred. It grows with your data without requiring more effort.
• It Creates a Single Source of Truth: By centralizing your data ingestion, you ensure your reports are built on a complete and consistent dataset, leading to more trustworthy insights.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Understanding the Basics: Append vs. Merge

In Power BI, "combining" data can mean two distinct things: appending or merging. It's important to know the difference so you use the right tool for the job.

Appending Queries is like stacking tables on top of each other. This is what you'll do when your files have the same columns but different rows of data. Think of it as adding January's sales data, February's sales data, and March's sales data into one long list. They all share columns like 'Date', 'Product', 'Quantity,' and 'Revenue'. This is the focus of our main tutorial.

Merging Queries is like joining tables side-by-side based on a common column. This is what you'd do if you had one file with sales data (including a 'ProductID') and another file with product details (like 'ProductName' and 'Category'), which you want to join using the common 'ProductID' column. It adds new columns, pulling information from one table into another.

Step-by-Step Guide: How to Merge Files from a Folder in Power BI

The most robust way to combine files with identical structures is by using Power BI's Folder connector. This tells Power Query to look inside a specific folder and treat every file it finds as part of a single dataset.

Step 1: Organize Your Source Files

Before you even open Power BI, proper organization is crucial. This single step prevents most common errors.

• Create a dedicated folder on your local drive or shared network where you will store all the files you want to combine. For example, C:\Reports\Monthly_Sales.
• Place only the files you intend to merge into this folder. While you can filter later, keeping the folder clean makes the process simpler.
• Most importantly: Ensure all files have the exact same structure. This means the column headers must be named identically and be in the same order. 'Sales Rep' is not the same as 'Sales_Rep'.

Step 2: Connect to the Folder

Now, let’s fire up Power BI Desktop.

1. On the Home ribbon, click Get Data.
2. In the menu that appears, select More....
3. In the Get Data window, select Folder from the list of connectors on the left, then click Connect.

You'll be prompted to provide the folder path. You can either paste the path directly or click Browse to navigate to the folder you created in Step 1.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Combine and Transform the Data

After connecting, Power BI will show you a metadata view of the files inside your folder. You'll see information like the file name, extension, date created, etc. You are not seeing the actual data yet, just a list of the contents.

At the bottom of this window, you have a few options. Since we need to stack these files, you’ll want to click the Combine & Transform Data button. The "Combine" part is what will append the files, and the "Transform Data" part will open the Power Query Editor, which is where you can further clean and prepare your data.

Do not click "Load" here. Loading would just import the list of file names, not the content inside them.

Step 4: Configure the 'Combine Files' Settings

This is where the magic happens. A new "Combine Files" dialog box will appear. Power Query is asking you to show it how to read one of the files, and it will then apply the same logic to all the other files in the folder automatically.

• Sample File: In the top-left dropdown, you can select which file Power Query should use as the template. Usually, "First File" is perfectly fine.
• File parsing: Power Query will then look inside that sample file. If it's an Excel file with multiple sheets, you'll be asked to select the correct sheet (e.g., 'Sheet1'). If it's a CSV, it'll show a preview of the data separated by delimiters.
• Select the table or sheet containing your data from the navigator on the left. A preview of the data will appear on the right.

Once you see the correct data, click OK.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 5: The Power Query Editor

Now you're in the Power Query Editor, and something interesting has happened. Power BI has performed a series of automated steps:

1. It created a consolidated query with the name of your folder (e.g., 'Monthly_Sales'). This query contains the combined data from all the files. Notice there's a new first column, 'Source.Name,' which tells you which of the original files each row came from. This can be very useful!
2. It also created a group of "helper queries" on the left panel, often tucked into a folder. These include the query that connects to the source file ("Sample File") and other transformation logic. For now, you can ignore these. Just work directly on your main, combined query.

Your data is now appended! From here, you can perform any additional data cleaning you need, such as:

• Checking that data types are correct (e.g., a 'Date' column is set to the Date type, not Text).
• Removing unwanted columns (like the 'Source.Name' column if you don't need it).
• Filtering out any unnecessary rows.

Once you are happy with the state of your data, click Close & Apply in the top-left corner of the Power Query Editor. This loads the combined data into your Power BI data model, ready for you to start building visuals.

Pro Tips for a Smoother Workflow

• Filter Your Folder Contents: Sometimes, folders contain other files (like temporary Excel files starting with ~$ or other log files). In the Power Query Editor, you can filter the "Source.Name" column to only include files that contain or end with .xlsx to prevent errors.
• Manage Headers Carefully: If one of your files has a slightly different header name, it can create a new column for that data, throwing off your table. The "Transform Sample File" query is where you can correct this. Any cleaning step you apply to the Sample File query will be applied to every file during the combination process.
• Check Data Types: Power BI tries to automatically detect data types, but it's not always perfect. Go through each column in the final query and confirm the data type is set correctly. This will prevent issues when you're creating calculations (DAX measures) later.

Final Thoughts

Learning how to connect to a folder in Power BI is a game-changer. It automates one of the most tedious manual parts of data prep, giving you a reliable and scalable process for consolidating reports. You can now spend your time analyzing insights instead of copying and pasting data.

Mastering tools like Power BI is a fantastic skill for creating robust reports from complex data sources. That said, we know that sometimes you need business-critical answers fast, an hour before a meeting, without needing to configure queries, models, or visualizations. That’s why we built Graphed. Our platform connects directly to your marketing and sales tools - like Google Analytics, Shopify, Facebook Ads, or HubSpot - and lets you create real-time dashboards and reports simply by describing what you need in plain English.