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)
You 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.
