Merge Data from Multiple Excel files into a Single 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)

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

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)