If you want to merge multiple Excel files into a single workbook, you can do it with Power Query.
This article will show you 2 techniques to do the job.
Step 1: Import from Folder
Power Query is a wonderful tool with many options to import and manipulate data.
- Open the option Get Data > From Files > From Folder.
- Then, select the folder name where are your Excel files.
- And the next dialog box shows you all the files in this folder and sub-folders.
- Click on the button Transform Data to continue the process of importation
Step 2: Select your files
For the moment, we have selected all the files in the selected folder and subfolders. Fortunately for us in this example, all the files have xlsx extensions. But, you can use the filter to select only the xlsx files.
- Right-click on the extension value (in a cell) with the information xlsx
- Then, the option Text Filters
- Finally, select the option Equals
Only the multiple Excel files are kept, ready to be import.
Step 3: Keep the column Content
Once you have filtered the files you want to combine, you must keep the column Content.
- Right-Click on the header of the column Content
- Select Remove Other Columns
And you have this
Step 4a: Easiest technique
Now, you can directly combine your file just by clicking on the arrow on the header of the column
Then, you select the Sheet name or the Table name to import. All the files will follow this rule.
And just like that, all your files are combined 😀👍
With this technique, Power Query generates 4 queries to merge many Excel files.
Step 4b: Write an M function
This second technique needs to use one M function because this option doesn't exist in the ribbon
- Go to the ribbon Add Column
- Then Custom column
And next, you insert the M function Excel.Workbook with the column Content as argument.
This function converts the contents of your file into a Table.
Step 6: Select the Data column
Behind a Table, there is more than one column to expand. With this technique, you expand the column Data
And now you have this
The column Content must be removed
Step 7: Expanded the column Data
In the final step, you just have to click on the icon Expand to visualize the content of all your Excel files.
This technique is 4 times faster than the first technique