Full outer join in Power BI

Let's assume that you have a 'Location' table:

ASCII
| Name     | City      |
|----------|-----------|
| Andrea   | Barcelona |
| Paula    | Calella   |
| Erik     | Barcelona |

And also the following 'Ages' table:

ASCII
| Name     | Age |
|----------|-----|
| Erik     | 33  |
| Sarah    | 30  |
| Michael  | 28  |

Let's assume that you want to merge both tables using PowerBI:

ASCII
| Name     | City      | Age  |
|----------|-----------|------|
| Andrea   | Barcelona | null |
| Paula    | Calella   | null |
| Erik     | Barcelona | 33   |
| Sarah    | null      | 30   |
| Michael  | null      | 28   |

If you try to do a full outter join, you will have undesired results:

ASCII
| Name   | City      | Age  |
|--------|-----------|------|
| Andrea | Barcelona | null |
| Paula  | Calella   | null |
| Erik   | Barcelona | 33   |
| null   | null      | 30   |
| null   | null      | 28   |

Sarah and Michael will appear as null, because they don't exist in the first table. To avoid it, after doing the outter join, select the name rather than the age:

ASCII
| Name   | City      | Ages.Name |
|--------|-----------|-----------|
| Andrea | Barcelona | null      |
| Paula  | Calella   | null      |
| Erik   | Barcelona | Erik      |
| null   | null      | Sarah     |
| null   | null      | Michael   |

Create a column that combines the first and third column:

DAX
= Table.AddColumn(
	#"Location", 
	"Combination", 
	each if [Name] <> null then [Name] else [Ages.name]
)

Result:

ASCII
| Name   | City      | Ages.Name | Combination |
|--------|-----------|-----------|-------------|
| Andrea | Barcelona | null      | Andrea      |
| Paula  | Calella   | null      | Paula       |
| Erik   | Barcelona | Erik      | Erik        |
| null   | null      | Sarah     | Sarah       |
| null   | null      | Michael   | Michael     |

Now delete columns Name and Ages.Name and rename Combination to Name.

ASCII
| Name    | City      |
|---------|-----------|
| Andrea  | Barcelona |
| Paula   | Calella   |
| Erik    | Barcelona |
| Sarah   | null      |
| Michael | null      |

At this point, you can do a full outter join selecting the age:

ASCII
| Name     | City      | Age  |
|----------|-----------|------|
| Andrea   | Barcelona | null |
| Paula    | Calella   | null |
| Erik     | Barcelona | 33   |
| Sarah    | null      | 30   |
| Michael  | null      | 28   |