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
- Right-click on the extension you want to keep
- Then, the option Text Filters
- 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.
- 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 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) 😉
- Go to the ribbon Add Column
- 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
14/11/2021 @ 08:46
Hi Frédéric Le Guen, thank you for sharing the tutorial. However the Power Query only import data from the webpage default table. Its unable to import data which need to be clicked in even though it in same webpage. Example (https://www.investing.com/equities/apple-computer-inc-income-statement) webpage it contains Quarterly and Annual table and Power Query only can import the Quarterly table but unable to import the Annual table. May i know how to import the Annual table from this webpage?
15/11/2021 @ 15:03
Hi, It's a very good question and, like you, I have never found the trick to load the 2 tables (quarterly and Annual). I don't know if there is a solution for this.