Forum Discussion
Power Query Table.NestedJoin bug
Fair enough, thats a bug. As I said earlier, it should have thrown an error. But it is still an edge case and I don't see how this " bricks" power query, because there is a very simple and efficient work-around to give you (after expanding) a cartesian product of Table 1 and Table2:
= Table.Addcolumn(Table1, "Full Table2", each Table2)
Super UserYou probably hit an edge case. The nestedJoin without any keys is probly not handled as you expect. Idon't even know what Iwould expect. A left outer join without any join keys does not seem very well defined I think.
If you want a cross you between table of you current Table (Source) with another table B do this:
Table.AddColumn(Source, "Joined Table", each B)I wouldn't spend any more time wondering why NestedJoin does not work. I am actually surprised that join does work. I guess the bug here is that NestedJoin should have thrown an error...
Fair point. However, it's not just an outer left join; it respects the join identities. I tested the following:
| JoinKind | Result | Rows |
| Inner | Cross product (all combinations of A and B) | m × n |
| FullOuter | Cross product (all combinations of A and B) | m × n |
| LeftOuter | Cross product (all combinations of A and B) | m × n |
| RightOuter | Cross product (all combinations of A and B) | m × n |
| LeftSemi | Distinct rows from Table A | m |
| RightSemi | Distinct rows from Table B | n |
| LeftAnti | Rows from Table A with no matches in Table B | 0 |
| RightAnti | Rows from Table B with no matches in Table A | 0 |
—and the results checked out.
As a last-ditch effort to make sure I wasn't crazy, I tried the same thing in SQLite:
CREATE TABLE user (
name varchar(20)
);
INSERT INTO user (name) VALUES ("Cole");
CREATE TABLE id (
id int
);
INSERT INTO id (id) VALUES (1);
SELECT * FROM user JOIN id;
—and got the exact same result.