Pinterest Pixel

Consolidate Multiple Excel Workbooks Using Power Query

One of the most sought after a query from the millions of Excel users around the world... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

One of the most sought after a query from the millions of Excel users around the world is:

How do I consolidate multiple Excel workbooks in Power Query into one?

There are a couple of ways you can do this, using VBA or complex formulas but the learning curve is steep and out of reach for most Excel users.

Luckily with Power Query Excel Workbook, this consolidation task can be done in a couple of minutes!  That’s right, only a couple of minutes.

Consolidate Multiple Excel Workbooks Using Power Query | MyExcelOnline

DOWNLOAD EXCEL WORKBOOK

I show you how below…

STEP 1: Create a New Folder on your Desktop or any directory and name it to whatever you like e.g. 2016 Sales

Move an Excel Workbook in this Folder that contains your Sales data e.g. January 2016.xlsx

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 2: Open a NEW Excel Workbook and go to Power Query > From File > From Folder

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 3: From the Folder dialogue box, click the Browse button

See also  Replicating Excel's LEFT Function with M in Power Query

Consolidate Multiple Excel Workbooks Using Power Query

This will bring up the Browse for Folder dialogue box and you need to select the folder you created in Step 1 and press OK.

This is how you can use Power Query load multiple files from folder feature.

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 4: This will open up the Query Editor.

From in here you need to select the first 2 columns (hold down the CTRL key to select) and Right Click on the column heading and choose Remove Other Columns

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 5: You need to go to Add Column > Add Custom Column

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 6: This will bring up the Add Custom Column dialogue box.

In here you need to name the new column E.G. Import, and within the Custom Column Formula you need to enter the following formula:

= Excel.Workbook([Content])

This will import the workbooks from within the Folder that you selected in Step 3

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 7: You now have a new column called Import.

Click on the Expand Filter and select the Data box only and press OK.  This will import the workbook from the folder

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 8: Click on the Expand Filter from the Import Data column and select OK.  This imports all the columns’ data from within the workbook

See also  Split the Time Using Power Query or Get & Transform

Consolidate Multiple Excel Workbooks Using Power Query

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 9: Now it is time to transform the data by making some cosmetic changes!

Remove the Content column by Right-Clicking and choosing Remove

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 10: 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 Using Power Query

 

STEP 11: Select the Date column and go to Transform > Data Type > Date

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 12: Select the Sales column and go to Transform > Data Type > Currency

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 13: Rename the column headings to whatever you like by double clicking on the column header, renaming and pressing OK

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 14: Go to File > Close & Load.

Consolidate Multiple Excel Workbooks Using Power Query

 

This will put the data into a new worksheet within your workbook

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 15: You can now Insert a Pivot Table to do your analysis by going to Insert > Pivot Table > New/Existing Worksheet

Consolidate Multiple Excel Workbooks Using Power Query

 

Put the Months in the ROWS and the Sales $ in the VALUES area:

See also  Case Insensitive Filtering Using Power Query or Get & Transform

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 16: NOW FOR THE COOL PART!!!!

You can move similar workbooks into the Folder we created in Step 1, say for subsequent months eg. February 2016.xlsx, March 2016.xlsx etc

NB: The Excel Workbooks have to have the same format and number of columns as in the workbook we imported in Step 1

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 17: In your Excel workbook, click on the imported data and this will open up the Workbook Queries pane (If this does not open, go to Power Query > Show Pane)

Click the Refresh button (or you can go to Table Tools > Query > Refresh)

Consolidate Multiple Excel Workbooks Using Power Query

 

STEP 18: This will import the February 2016.xlsx and March 2016.xlsx data into the Excel workbook and append it to January’s data

Consolidate Multiple Excel Workbooks Using Power Query

STEP 19: Now you can Refresh the Pivot Table and the new imported data will be reflected

Consolidate Multiple Excel Workbooks Using Power Query

 

Next month all you have to do is drop in the new month’s workbook into the 2016 Sales Folder and Refresh the Query & the Pivot Table to see the results!

THAT IS POWER!!!!

In the article, you have learned how to use Power Query combine multiple Excel files feature and its advantages. To learn more about Power Query, Click here.

See also  Power Pivot is now available in all versions of Excel

Further Learning:

PIVOT BANNER

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!