Full outer join
One of the join kinds available in the Merge dialog in Power Query is a full outer join, which brings in all the rows from both the left and right tables. More information: Merge operations overview
Screenshot shows a left table with Date, CountryID, and Units columns. The emphasized CountryID column contains values of 1 in rows 1 and 2, 3 in row 3, and 2 in row 4. A right table contains ID and Country columns. The emphasized ID column contains values of 1 in row 1 (denoting USA), 2 in row 2 (denoting Canada), 3 in row 3 (denoting Panama), and 4 (denoting Spain) in row 4. A table below the first two tables contains Date, CountryID, Units, and Country columns. All rows are rearranged in numerical order according to the CountryID value. The country associated with the CountryID number is shown in the Country column. Because the country ID for Spain wasn't contained in the left table, a new row is added and the date, country ID, and units values for this row are set to null.
This article uses sample data to show how to do a merge operation with the full outer join. The sample source tables for this example are:
Sales: This table includes the fields Date, CountryID, and Units. CountryID is a whole number value that represents the unique identifier from the Countries table.
Countries: This table is a reference table with the fields ID and Country. The ID field represents the unique identifier for each record.
In this example, you merge both tables, with the Sales table as the left table and the Countries table as the right one. The join is made between the following columns.
| Field from the Sales table | Field from the Countries table |
|---|---|
| CountryID | ID |
The goal is to create a table like the following, where the name of the country appears as a new Country column in the Sales table. Because of how the full outer join works, all rows from both the left and right tables are brought in, regardless of whether they only appear in one of the tables.
Screenshot of the final full outer join table with Date, a CountryID, and Units derived from the Sales table, and a Country column derived from the Countries table. A fifth row was added to contain data from Spain, but that row contains null in the Date, CountryID, and Units columns since those values didn't exist for Spain in the Sales table.
To perform a full outer join:
- Select the Sales query, and then select Merge queries.
- In the Merge dialog box, under Right table for merge, select Countries.
- In the Sales table, select the CountryID column.
- In the Countries table, select the ID column.
- In the Join kind section, select Full outer.
- Select OK
Note
You can merge on more than one column by selecting and holding Ctrl and then selecting the columns.
Tip
Take a closer look at the message at the bottom of the dialog box that reads "The selection matches 4 of 5 rows from both tables." This message is crucial for understanding the result that you get from this operation.
In the Countries table, you have the Country Spain with ID of 4, but there are no records for CountryID 4 in the Sales table. Therefore, only three of four rows from the right table found a match. All rows from the right table that didn't have matching rows from the left table are grouped and shown in a new row in the output table with no values for the fields from the left table.
From the newly created Countries column after the merge operation, expand the Country field. Don't select the Use original column name as prefix check box.
After performing this operation, you'll create a table that looks like the following image.
Screenshot of the full outer join final table containing Date, a CountryID, and Units derived from the Sales table, and a Country column derived from the Countries table. A fifth row was added to contain data from Spain, but that row contains null in the Date, CountryID, and Units columns since those values didn't exist for Spain in the Sales table.