Forum Discussion

colecrouter's avatar
colecrouter
Frequent Visitor
1 year ago
Solved

Power Query Table.NestedJoin bug

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 "cro...
General Comment
Need Help
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    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)

     

v-karpurapud's avatar
v-karpurapud
Community Support
1 year ago

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.

  • colecrouter's avatar
    colecrouter
    Frequent Visitor
    1 year ago

    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.

    • PwerQueryKees's avatar
      PwerQueryKees
      Super User
      1 year ago

      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. 

      • colecrouter's avatar
        colecrouter
        Frequent Visitor
        1 year ago

        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.