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 UserJoin and Nested Join don't do Cartesian product. The SQL jou showed produces an Cartesian product. What is your point? Showing that powerquery has an undocumented feature?
Thanks for correcting me on the technicality. Every SQL dialect I've ever used has never put up a fight, so I hadn't realized this was "technically not a join".
My first guess would be that under the hood, the engine is doing a simple nested loop join, which wouldn't care if there was no index column.
Not that it's entirely relevant, but humor me and let me make sure I understand correctly. Consider the following:
| ID | Name |
| 1 | John Smith |
| ID | Age |
| 1 | 42 |
If I did this
Table.Join(Table1, {"ID"}, Table2, {"ID"})
I would get this:
| ID | Name | Age |
| 1 | John Smith | 1 |
Now if I add an "identity" column to both tables, e.g.:
Table.AddColumn(Table1/Table2, "Identity", each true)
| Identity |
| True |
If I run the following:
Table.Join(Table1, {"ID", "Identity"}, Table2, {"ID", "Identity"})
I get the same result. So if a join on {"ID"} is equivalent to a join on {"ID", "Identity"}, then it stands to reason that a join on {"Identity"} would be equivalent to on {}, right? Every resource I've since come across says that this isn't the case.
- PwerQueryKees
This works the same in SQL and PowerQuery. Your example has an Identity that always has the value True. True = True. Always. So including the Identity in the join does not make a difference because of your data. If you add a row to each table where identity = False, removing Identity from the join WILL make a difference.
Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.