Forum Discussion

rjrn's avatar
rjrn
Frequent Visitor
8 years ago
Solved

What to do when Merge JoinKind.RightAnti returns NO rows?

Hi all!

 

I am trying to make a Merge JoinKind.RightAnti on 2 queries to get only the rows that are in the 2nd query. Most of the times it works, since there ARE rows in the 2nd query that ARE NOT in the 1st query.

 

The issue is when all rows from the 2nd query ARE in the 1st query. Then I get an error:

Expression.Error: There weren't enough elements in the enumeration to complete the operation

How do I handle this error so that I can still use the Merge query in other queries? How do I get a blank row instead of an error?

 

Any help is very much appreciated!

 

Regards,

RN

  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    An empty Table2 means Table2 without records.
    So you have the columns and their data types, so the rest of your code runs without errors.

     

    My solution translated to your query (not tested, but it should be OK):

     

    let
    Source = Table.NestedJoin(CW_opps,{"Opportunity Id18"},PW_opps,{"Opportunity Id18"},"PW_opps",JoinKind.RightAnti),
    PW_opps1 = try Source{0}[PW_opps] otherwise Table.SelectRows(PW_opps, each false),
    #"Removed Columns" = Table.RemoveColumns(PW_opps1,{"Proposal Due Date","Stage"})
in
    #"Removed Columns"

4 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    8 years ago

    It depends on your query code.

     

    Suppose your "Merge1" query is only the merge and nothing else::

     

    = Table.NestedJoin(Table1,{"ID"},Table2,{"ID"},"Table2",JoinKind.RightAnti)

     

     

    And if your followup query refers to Merge1[Table2](0), then you can use try .. otherwise and have an empty Table2 returned in case of an error.:

     

    = try Merge1[Table2]{0} otherwise Table.SelectRows(Table2, each false)
    • rjrn's avatar
      rjrn
      Frequent Visitor
      8 years ago

      Thank you!

       

      Here is the Merge1:

      let
    Source = Table.NestedJoin(CW_opps,{"Opportunity Id18"},PW_opps,{"Opportunity Id18"},"PW_opps",JoinKind.RightAnti),
    PW_opps1 = Source{0}[PW_opps],
    #"Removed Columns" = Table.RemoveColumns(PW_opps1,{"Proposal Due Date","Stage"})
in
    #"Removed Columns"

      Sorry for the stupid questions, but what do you mean with "have an empty Table2 returned in case of an error"?

       

      Regards, RN

      • MarcelBeug's avatar
        MarcelBeug
        Icon for Community Champion rankCommunity Champion
        8 years ago

        An empty Table2 means Table2 without records.
        So you have the columns and their data types, so the rest of your code runs without errors.

         

        My solution translated to your query (not tested, but it should be OK):

         

        let
    Source = Table.NestedJoin(CW_opps,{"Opportunity Id18"},PW_opps,{"Opportunity Id18"},"PW_opps",JoinKind.RightAnti),
    PW_opps1 = try Source{0}[PW_opps] otherwise Table.SelectRows(PW_opps, each false),
    #"Removed Columns" = Table.RemoveColumns(PW_opps1,{"Proposal Due Date","Stage"})
in
    #"Removed Columns"