Combine multiple files (binaries) in Power BI or Folder Connector in Power BI

Combining multiple Excel files (or any structured files) using the Folder connector in Power BI is a powerful way to automate data loading and appending — especially useful when you have monthly or yearly files with the same schema.

Scenario:

We have multiple Excel files, like:

• Students_2020.xlsx
• Students_2021.xlsx
• Students_2022.xlsx
• Students_2023.xlsx

Each contains the same structure:

• Year
• Student ID
• Name
• Branch

Goal: Use Power BI’s Folder Connector to:

• Load all files from a folder
• Combine/append their data
• Optionally extract the file name (e.g., to track year/source)

Combine Multiple Files Using Folder Connector

Step 1: Place All Files in a Folder Put all Excel files in one folder on the computer (e.g., D:\StudentFiles\).

Step 2: Power BI Desktop → Get Data → From Folder
• Go to Home > Get Data > Folder
• Select the folder path (D:\StudentFiles\)
• Click OK

Step 3: Power BI shows list of files It shows the list of all the files in the folder, now click on Transform Data which open the power query editor.

In the Power query editor, we can filter the files based on the extension, name or any other custom way.

Internally the Folder connector use the Folder.Files M function.

To combine binary files in Power Query Editor, select Content (the first column label) and choose Home > Combine Files. Or we can just select the Combine Files icon next to Content.

A dialog box for Combine Files opened. Select the Sample File and Sheet names which we want to select from each file.

We have the option to skip the files with errors. After that click on Ok.

Now the Power BI automatically, do the transformation on the Sample File, as shown in the image below:

Also, it creates a function query which is linked to the query of the Transform Sample File. So whatever transformation we still further applied to the Transform Sample File is automatically reflected in the function Transform File.

Also, the Power BI applied this function to the binary input of each row of the original table.

After that the column name is changed from Name to Source.Name.

After that we select only two columns required further and then expand the table to get all the rows from the table.

Finally, we received the output which is the appending all the table tows in one table.

Here, all the transformation steps are automatically applied by the Power BI Combine operation, but we can customize any transformation step according to our requirement. So, we can easily combine all files within a given folder if they have the same file type and structure (such as the same columns). If we add or remove files from the folder the Power BI automatically fetches the latest data from the folder when we refresh our query in Power BI.