Combining Excel files stored in a folder with Power BI

Introduction

The ability to combine data from multiple sources is essential to create actionable insights that can help drive a business decision or process.

In this article, we’ll introduce two different approaches to combine Power BI Excel files stored in a folder on our local machine. We’ll also present some good practices that should be considered throughout the developments.

Scenario

Let’s look at this example:

I have a folder on my computer that contains different excel files. As you can see from the image below, some of them have the word “NEW” in the name, while others have the word “OLD”.

For this example, we’ll import all the files to Power BI, however I’ll show you how you can select some of the files, filtering in Power Query only the ones that contain the word “NEW”, for example.

The content of the files looks like this:

Importing data

The first step to import data from excel files stored in a folder is to choose the Get Data option, select the Folder connector and click on the Connect button.

Then browse to the files location and click OK.

After that, we’ll see a screen that shows all files in the selected folder.

As I mentioned before, for this example, we just need to consider the files that contain the word NEW in the name. This way, we need to choose the “Transform Data” option and apply some transformations with Power Query, before combining the files.

Once in Power Query, we can apply a filter as shown below:

After applying this filter, we already have the files we need.

Now it’s time to present two different approaches to combine the content of the selected files!

Combining files

Solution 1 – Use combine option on Power Query

The first approach is to combine the files content automatically by clicking on the Combine option.

Then we’ll see a screen that gives us some options to change how files are combined, but in most cases, we just need the select the default and click OK.

After clicking on the OK button, Power BI will create the queries to combine the files. However, notice that each time we combine the files in this way, additional queries are generated and stored in the folder “Transform File from…”, as you can see from the image below.

To avoid this to happen, there is another approach that we will present next.

Solution 2 – Create a customized column

Before presenting an alternative way to combine the files, let’s go back to the step where we filter the files.

Then, for a better understating we will remove the columns that are not necessary and keep only the Content and Name columns.

Now the trick is to create a customized column to get the binary content that is in the “Content” column.

To do that, go to “Add Column” tab, choose “Custom Column” option and enter the following formula: Excel.Workbook([Content])

Note that “Content” means the name of the column containing the binary data. This step will create a new column containing table records, to get the content from our files.

Then, expanding “Data” attribute we can see a preview of the content from our excel files.

Note that the headers are not automatically promoted as headers, so an additional trick is to change the function to include an additional parameter:

Excel.Workbook ([Content], true)

When we do it, the headers are automatically promoted, and it avoids an additional step on Power Query.

Finally, we just need to expand the attributes.

Additional tips

1) Rename columns easily

After obtaining the content from the excel files we can do additional transformations with some easy tricks.

For example, in this case, I want to avoid having one more step to rename the columns. I can rename them directly in the formula bar.

Look at this example:

2) Use parameters

Another trick and a good practice we should consider is the use of parameters to store the location path of our data source. This way if the path is changed later, we just have to update the parameter and the query is updated automatically.

For example:

Conclusion

Combining excel files in Power BI is a great way to make sense of your data. This makes it easier to provide insights and make good decisions.

Power BI is a powerful tool and allows us to obtain the same result in different ways, but whenever possible we should try to improve the work efficiency using simple tricks and ensure the use of good practices.