Import Many Workbooks in a single one

Reading time: 3 minutes
Last Updated on 11/05/2021 by Frédéric LE GUEN

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.

Menu Import from Folder

Then, you simply have to select the folder name where are your Excel files.

Select a folder with the files to combine

And the next dialog box shows you all the files in this folder and sub-folders.

Files in the folder with Power Query

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
Apply filter to select your files

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
Remove other columns except the Content columns

And you have this

The column Content is the only column you kept

Step 4a: Easiest technique

Now, you can directly combine or your file just by clicking on the arrow on the header of the column

Icon to combine all the files

Then, you select the Sheet name or the Table name to import. All the files will follow this rule.

Windows to select the object to import

And just like that, all your files are combine 😀👍

Queries automatically genereted to combine many Excel workbooks

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
Power Query Add custom column

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

=Excel.Workbook([Content])

Excel.Workbook function

This function convert the contain of your file as a Table.

Excel file are converted as 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

Expand the column Data of the Table

And now you have this

Result after the selection of the column Data

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

All Excel files combine with the M function

This technique is 4 times faster than the first technique

2 Comments

  1. Steven
    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?

    Reply

    • Frédéric LE GUEN
      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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Import Many Workbooks in a single one

Reading time: 3 minutes
Last Updated on 11/05/2021 by Frédéric LE GUEN

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.

Menu Import from Folder

Then, you simply have to select the folder name where are your Excel files.

Select a folder with the files to combine

And the next dialog box shows you all the files in this folder and sub-folders.

Files in the folder with Power Query

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
Apply filter to select your files

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
Remove other columns except the Content columns

And you have this

The column Content is the only column you kept

Step 4a: Easiest technique

Now, you can directly combine or your file just by clicking on the arrow on the header of the column

Icon to combine all the files

Then, you select the Sheet name or the Table name to import. All the files will follow this rule.

Windows to select the object to import

And just like that, all your files are combine 😀👍

Queries automatically genereted to combine many Excel workbooks

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
Power Query Add custom column

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

=Excel.Workbook([Content])

Excel.Workbook function

This function convert the contain of your file as a Table.

Excel file are converted as 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

Expand the column Data of the Table

And now you have this

Result after the selection of the column Data

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

All Excel files combine with the M function

This technique is 4 times faster than the first technique

2 Comments

  1. Steven
    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?

    Reply

    • Frédéric LE GUEN
      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.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *