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.
POWER BI FOR DUMMIES
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.
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.
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.
Step 10: Rename Column Headers
After you rename the column headers, you will get the desired output data as shown in the below picture.
You can acquire more knowledge:
- Consolidate Multiple Excel Sheets
- Unpivot Data Using Excel Power Query
- Split First & Last Name Using Power Query
- Learn How to Replace Values in Power Query
