If you want to import and combine many workbooks in a single one, using Power Query is the best tool. But there is 2 techniques and the performance on the memory is really different between them.

Step 1: import from folder

Power Query is a wonderful tool with a lot of options to import or manipulate data. One of these options allows you to import the files from a folder.

Then, you simply have to 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 collect all the files in the selected folder and subfolders. Fortunately for us in this example, all the files have and xlsx extensions. But, you can, at this step, apply easily a filter for any columns.

For instance, if you want to secure only the xlsx files in your selection, you just have to

1. Right-click on the extension you want to keep
2. Then, the option Text Filters
3. Finally, select the option Equals

Step 3: Keep the only the column Content

Once you have filtered the files you want to combine, you must keep the column Content.

1. Right-Click on the header of the column Content
2. Select Remove Other Columns

And you have this

Step 4a: Easiest technique

Now, you can directly combine or 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 combine 😀👍

With this technique, Power Query generates 4 queries that will to combine the files. It's very convenient but if you need to reload frequently your process, it will take a lot of time.

Step 4b: M Code technique

This second technique needs to use one M function (because you don't have this option in the ribbon) 😉

1. Go to the ribbon Add Column
2. Then Custom column

And next, you insert the M function Excel.Workbook with the column Content as argument.

=Excel.Workbook([Content])

This function convert the contain of your file as 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

Final step, you just have to click on the icon Expand to visualize the contain of all your Excel files

This technique is 4 times faster than the first technique