Merge and Append in Power Query

The process for Merge and Append are essentially the same.  This walks through Merge. 

Merge = Combines two or more files based on one or more matching columns so that their shared data appears on one row.  In this example, starting with 1085 rows, and another with 1082 rows, we will end up with 1085 rows. 

Append = Simply combines (add rows from one to another) two or more files and matches columns where they have the exact same name.  In this example, starting with 1085 rows, and another with 1082 rows, we would end up with 2167 rows. 

1. In Excel, 
1. Open a Blank Workbook 
2. Choose the Data menu 
   
   
   
   
   
1. Choose Get Data 
1. > from File 
2. > from Excel Workbook 
1. Select a file from the window that opens 
2. In the subsequent window, click the appropriate tab from that Excel file
   
   
   
   
   
3. Choose Transform Data
1. Power Query Editor window opens 
   
   
   
   
   
2. In Power Query Editor, in the New Query section 
   
   
   
   
   
1. Choose New Source  
1. > File 
2. > Excel Workbook 
1. Select a file from the window that opens 
1. In the subsequent window, click the appropriate tab from that Excel file, and click OK 
2. Two reports now appear in the Queries pane (left) 
   
   
   
   
   
2. In the Combine section,  
1. Choose the drop down next to “Merge Queries” 
2. Choose Merge Queries as New
   
   
   
   
   
1. A new Merge window opens, with one of the Excel files already chosen 
   
   
   
   
   
2. Choose the other Excel file from the drop-down in the middle 
   
   
   
   
   
   
3. Begin choosing which data columns to match on 
1. Click the column name from the top sample, then click the matching column name in the bottom sample.  Both will highlight when selected.
2. Choose columns that contain the exact same information.  Both tables should contain matches, though not all will match.  In this example, the match is between “IDF Form ID” and “Individual Data form ID,” because they will contain the same data. 
1. If matching on more than one column, CTRL+Click the second column 
   
   
   
   
   
4. Choose the Join Kind at the bottom. 
1. If unsure, choose “Full Outer” to keep all data from both Excel sheets
5. Click OK 
1. The Merge window disappears, and a new table appears in the Queries in the left panel.  This is file containing the merged files. 
   
   
   
   
   
6. Click Close & Load 
1. The window returns to the initial Excel Workbook and loads the merged files. 
3. In the Excel Workbook, the right panel will highlight which file is currently being viewed.