Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Apologies if this has been commented on before; I searched but am afraid there are many ways to describe this behavior.
When I run this query below, I get exactly what I expect; an effective "cross-join".
= Table.Join(#"Removed Other Columns", {}, Descriptions_Input, {}, JoinKind.LeftOuter)
If you try the same with a nested join:
= Table.NestedJoin(#"Removed Other Columns", {}, Descriptions_Input, {}, Output, JoinKind.LeftOuter)
—it works, but only after the next step... As in, the resulting table has no extra columns. However, insert a step after this, and the column will miraculously appear.
Has anyone else encountered this before? What's your breakdown of the issue?
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 @colecrouter
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Would you consider this post resolved?
Putting aside what the "correct behavior" or "expected outcome" is (as other commenters have graciously contributed), there are still multiple issues at play, including one that completely bricks Power Query.
I don't agree. What is the bug? It does not behave as you expect and it does not behave exactly as you would want it to. And it does not work like SQL.
Neither are bugs. You have not pointed out any behaviour inconsistent with the documentation.
Kindly see the following example:
let
Names = Table.FromRecords({[Name="Cole"]}),
IDs = Table.FromRecords({[ID=1]}),
// Our notorious operation
Joined = Table.NestedJoin(Names, {}, IDs, {}, "IDs")
in
Joined
ID column is missing.
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
The column has now appeared and is accessible. Let me know if you are not seeing the same thing.
When I came across this, I was debugging an error for a coworker (100K+ row spreadsheet). They had deleted a redundant step between a Table.NestedJoin and a Table.ExpandTableColumn, causing the Table.ExpandTableColumn to fail for seemingly no reason.
Hi @colecrouter
We found the answer provided by @PwerQueryKees to be correct. Please consider trying it out to check if it works for you.
If they work, please consider marking them 'Accept as Solution', if not please share the details.
Thank You!
Join 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.
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.
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.
Hi @colecrouter
Hope you are doing well!
Could you please confirm if your query have been resolved the solution provided by @Ilya_K . If they have, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.
Thank you
No, they have not provided a course of action to try.
@v-karpurapud are you able to try the basic example in the comment above?
Hi,
That's not an issue, but different functions Table.Join and Table.NestedJoin.
Table.Joint expands automatically all columns and Table.NestedJoin requires additional step where you choose which columns you want to be expanded.
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.