Skip to content
Related Articles
Open in App
Not now

Related Articles

How to Merge Content of All Files in Folder with Power Query?

Improve Article
Save Article
Like Article
  • Last Updated : 27 Jan, 2023
Improve Article
Save Article
Like Article

Use Power Query to create a single table from numerous files with the same schema that are saved in the same folder. For instance, you could wish to merge budget workbooks from several departments each month when the columns are the same but the workbooks have different numbers of rows and values. Once it is configured, you may add further transformations, just as you would with any other single imported data source, and then refresh the data to view the results for each month.

Merging Content of All Files

Suppose we have multiple files in a folder that contains consistent types of data, with the help of power query we can merge all the data together with the help of power query easily. Let’s say there is a folder “Student” that contains several files that contain information about the students in the form of a table.



We need to merge all the student details from all the files into a single file. As far as all the files are consistent, which means hold similar kind of tables only, merging all the details are very easy.

Steps to Merge Content of All Files in Folder with Power Query

Step 1: Open Excel and then go to the Power Query on the top of the ribbon and then select From File and then in the dropdown choose From Folder. If you are using excel 2016 or higher then there is an option Get & Transform Data option instead of Power Query.



And then browse to the folder where you have stored the files.

Step 2: A table appears containing all the information & metadata about our files if you want to keep all the columns you can click load else you can click edit to edit your contents according to your needs.



When you click on edit a query editor opens up which lets you edit the contents, you can remove all the redundant and unwanted columns and rows easily.



Make sure the data types of every column are consistent.

Step 3: We can remove the unwanted columns and rows and edit our data with the help of power query and filter only our desired files based on extension types.



If you want to see the VBA codes you can open the advanced editors and perform queries from there too.

Step 4: On the contents column, there is a button that helps combine all the files data, this combines all the files one by one row-wise.



Step 5: At last you can close and load your final table into the workbook.



My Personal Notes arrow_drop_up
Like Article
Save Article
Related Articles

Start Your Coding Journey Now!