Merge Data from multiple excel files into a single Excel Workbook

One of the most common data cleaning tasks is consolidating or merging data from multiple excel files into a single master excel file. Traditionally, either people have been doing it manually or by using a VBA code.

The problem is

  1. Manually opening all excel files and pasting data into a single file takes all the time in the world
  2. VBA is Strict – If there is any variability, the code tanks plus it is slow on large workbooks

 

Enters the new hero Power Query!  In this post I am going to share 2 methods to create a Master Data by combining multiple Excel Files together

  1. When Data Structure is Even  – All the source excel files have the same structure (i.e. column headers)
  2. When Data Structure is Uneven – The excel files do not have all matching columns. There could be extra columns, extra rows and a bunch of other junk

 

#1 Merging Data from Multiple Excel Files (even data structure)

DOWNLOAD THE EXCEL FILE 

 

 

#2 Merging Data from Multiple Excel files (uneven data structure)


DOWNLOAD THE EXCEL FILES USED

 

Other PowerQuery Tutorials

  1. Rank Data Based on 2 Conditions
  2. Vlookup in PowerQuery
  3. Repeat Row N times
  4. Convert multiple columns in Rows (Unpivot Data)

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI