7 Ways to Merge Your Data

7 Ways to Merge Your Data

An overview of the different join types available in Power Query.

When to use

When you need to merge specific data from two different tables into a new table.

Instructions

Creating a Join

• Create a staging (connection only) query for each data source to be merged
• Go to the Queries pane --> right click one of the tables --> Merge
• Select the common column on which to merge, and select the Join type from the drop-down list

The 7 Join Types

1. Left Outer Join
   • Returns all the records from the left-hand (top) table with any matching records from the right-hand (bottom) table
2. Right Outer Join
   • Returns all the records from the right-hand (bottom) table with any matching records from the left-hand (top) table
3. Full Outer Join
   • Returns all the records from both tables
4. Inner Join
   • Returns only the records that match in both tables
5. Left Anti Join
   • Returns only the records from the left-hand (top) table that do NOT have matches in the right-hand (bottom) table
6. Right Anti Join
   • Returns only the records from right-hand (bottom) table that do NOT have matches in the left-hand (top) table
7. Full Anti Join
   • Returns records that do NOT have matches in the other table
   • Must be built manually by appending Left Anti and Right Anti join results

Hints & tips

• Matches that return “missing” data (i.e. mismatches between the two tables) are filled with a null value
• When expanding the ‘Joined’ table column, not all columns need to be selected