Acquire Knowledge in How to Consolidate Multiple Excel Workbooks in Power Query

The limitation of Excel of consolidating multiple Excel workbooks into one has overcome by Power Query. Power Query can do this consolidation task in a couple of minutes. This is explained below within a few easy steps.

MICROSOFT POWER QUERY FOR BEGINNERS
• Course Certificate • Lifetime Access & Free Updates • Lifetime Support • Career Focused

Table of Contents

Consolidate Multiple Excel Workbooks By Power Query

Step 1: Open Excel Workbook from Folder

Suppose you have an Excel Workbook that contains your Sales data in a Folder. You have to choose this file from this folder into your Power Desktop as per the instructions are given below.

Home > Get Data > More > Folder > Connect

Step 2: Add Folder Path

After you connect to the folder, click on Browse option and provide the link into the folder path area and click OK to import the file as shown below.

Step 3: Remove the Unwanted Columns

After you add folder path, this will take you to the Power Query Editor. From there you need to select the first 2 columns and Right Click on the column heading and choose to Remove Other Columns option as given in the below picture.

Step 4: Add Custom Column

To import the workbooks from within the Folder that you selected in Step 2, you need to go to-

Add Column > Custom Column

Step 5: Add a New Column

This will bring up the Add Custom Column dialogue box. Here, you need to name the new column and within the Custom Column Formula, you need to enter the following formula to import the workbooks from within the Folder.

= Excel.Workbook([Content])

Step 6: Import the Workbook from the Folder

You now have a new column called Import. Click on the Expand Filter of the import column and select the Data box only and press OK as shown in below picture. This will import the workbook from the folder.

Step 7: Expand the Data File

After you Import the Data sheet, click on the Expand Filter from the Import. Data column and select OK. This imports all the columns’ data from the workbook as shown below.