Full Outer Join not working in Version 1707 (Build 8231.1000)

[Stevenson Yu]

Assume there two tables.

One table contains the following:

• A
• B
• C

The other table contains the following:

• D
• E
• F

A full outer join, after expansion, should create a new table with two columns that look like this:

• A D
• A E
• A F
• B D
• B E
• B F
• C D
• C E
• C F

Instead, this is returned:

• A null
• B null
• C null
• null D
• null E
• null F

[#2 · Accepted solution]

Hi Stevenson,

That's correct behaviour for Full Outer join, it returns records from both tables with null in other column for not-matching records.

See, for example, http://www.dofactory.com/sql/full-outer-join

[#3]

Thank you for your reply. After more searching, I realized that I was barking up the wrong tree and there was nothing wrong with Full Outer Join. I was confusing it with a Cartesian Join.

[#4]

No problem. Cartesian join is not predefined, if you'd like to have it you may use something like

...
 CreateCartesian = Table.AddColumn(stepBefore, "New Column", each Table2),
 ExpandTable = Table.ExpandTableColumn(CreateCartesian, "New Column", {"Column1"}, {"Column2"})
...

other words adding Table2 to each row of Table1