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)
Hi Ilya, let me clarify.
Load the following code:
let
Names = Table.FromRecords({[Name="Cole"]}),
IDs = Table.FromRecords({[ID=1]}),
Joined = Table.NestedJoin(Names, {}, IDs, {}, "IDs")
in
Joined
Notice how the "IDs" field is absent. Now, add any extra step:
let
Names = Table.FromRecords({[Name="Cole"]}),
IDs = Table.FromRecords({[ID=1]}),
Joined = Table.NestedJoin(Names, {}, IDs, {}, "IDs"),
// Useless operation
Sorted = Table.Sort(Joined, {{"Name", Order.Ascending}})
in
Sorted
Now, the "IDs" field is present.
Hopefully that makes more sense.
Hi,
It's a good point.
Power Query works based on lazy evalution. In your example, if you are not going to use joined table, then Power Query will not spend resources on it.
I do not make joins right in code, I use button "Merge" and it always retuns to me the second screenshot without any "useless" step, so I never faced this behaviour.
https://learn.microsoft.com/en-us/powerquery-m/evaluation-model
"List, Record, and Table member expressions, as well as let expressions (Go to Expressions, values, and let expression ), are evaluated using lazy evaluation. That is, they are evaluated when needed. "
This is not the case, sadly. If you attempt to use the column, it will give you an error. The column is simply gone, until the next step.