fbpx

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.

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

OPEN EXCEL WORKBOOK FROM FOLDER

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.

 ADD FOLDER PATH

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.

REMOVE UNSELECTED COLUMNS

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

 ADD 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])

CUSTOM COLUMN WINDOW

Step 6Import the Workbook from the Folder

You now have a new column called ImportClick 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.

USE EXPAND FILTER TO SELECT COLUMNS

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.

USE EXPAND FILTER TO SELECT COLUMNS

ALL COLUMNS DATA IMPORTED WITHIN THE WORKBOOK

Step 8: Remove the Content Column

Now it is time to transform the data. To do so, remove the Content column by Right Clicking and choosing Remove option.

REMOVE THE CONTENT COLUMN

Step 9: Filter Out the Value

Select the Import.Data.Column1 and filter out the CUSTOMER heading and press OK.  This will also remove the other column’s headers.

, Consolidate Multiple Excel Workbooks in Power Query

Step 10: Rename Column Headers

After you rename the column headers, you will get the desired output data as shown in the below picture.

 AFTER RENAMING COLUMN HEADERS

You can acquire more knowledge: 

Leave a Reply