Open In App

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

Improve
Improve
Like Article
Like
Save
Share
Report

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.

folder-student

 

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.

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.

browse-for-folder

 

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.

editing-query-editor

 

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.

removing-unwanted-columns

 

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.

combining-all-files-data

 

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

loading-final-table

 


Last Updated : 27 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads