Joins
M Table Joins
Joining Tables
M table joins use Table.Join with inner or left outer types.
Introduction to M Table Joins
In Power Query M, table joins are essential for merging data from different tables based on a common column. The Table.Join function is used primarily with two types of joins: inner join and left outer join. Understanding these join types allows you to effectively combine datasets in a meaningful way.
Inner Join Explained
An inner join returns only the rows that have matching values in both tables. This is useful when you need data that exists in both sources.
In the example above, the InnerJoinResult will include only the row with ID = 1 because it is the only ID present in both Table1 and Table2.
Left Outer Join Explained
A left outer join returns all rows from the left table and matched rows from the right table. If there is no match, the result is null for columns from the right table.
For the left outer join example, LeftJoinResult will include all rows from Table1. The row with ID = 2 will show null for the Age column because there is no matching ID in Table2.
Choosing Between Inner and Left Outer Joins
When deciding between an inner join and a left outer join, consider the desired outcome for unmatched rows. Use an inner join when you need only the intersecting data. Opt for a left outer join when retaining all entries from the left table is crucial, regardless of matches in the right table.
Joins
- Table Joins
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Previous
- Conditional Columns
- Next
- Inner Join
