Full Anti Join (M not DAX)

Hi, 

It seems you're looking for a 'Full Anti-Join'. And even though this option is unavailable in the user-interface, there are 4 great methods you can use to achieve this.

1. Perform two left-anti joins (just swap around the table names), and then join them back together. 

let
  LeftAntiT1 = Table.NestedJoin(
                  Table1,    {"Key"},         
                  Table2,    {"Key"},      
                  "Table2",  JoinKind.LeftAnti // Join type
               ),
  LeftAntiT2 = Table.NestedJoin(
                  Table2,    {"Key"},         
                  Table1,    {"Key"},      
                  "Table2",  JoinKind.LeftAnti // Join type
               ),
  CombinedTable =     Table.Combine( { LeftAntiT1 , LeftAntiT2 } ),
  RemoveMergeColumn = Table.RemoveColumns( CombinedTable, {"Table2"} )
in
   RemoveMergeColumn

Another way would be to do a 'Full Outer Join' and filter the Table1 columns to be null or Table2 columns to be null

let
  FullOuterJoin =           // Perform a full outer join
    Table.NestedJoin(
        Table1,    {"Key"},
        Table2,    {"Key"},
        "Table2", JoinKind.FullOuter
    ),
  ExpandColumns =           // Retrieve all values from both tables
     Table.ExpandTableColumn(
         FullOuterJoin, "Table2",
         {"Key", "T2"}, {"Key.1", "T2"}
     ),
  T1orT2isNull =            // Keep rows where T1 or T2 = null
     Table.SelectRows( ExpandColumns, each [T1] = null or [T2] = null),
  MergeKeys =
    Table.CombineColumns(   // Combine Key Column values
        T1orT2isNull,
        {"Key.1", "Key"},
        Combiner.CombineTextByDelimiter("", QuoteStyle.None),
        "Key"
    )
in
  MergeKeys

A third way (and the cleanest one, code wise) is by using Table.RemoveMatchingRows: 

Table.RemoveMatchingRows(
      Table1,
      Table.ToRecords(Table2[[Key]]), {"Key"}
   )
  & Table.RemoveMatchingRows(
      Table2,
      Table.ToRecords(Table1[[Key]]), {"Key"}
   )

The 4th method makes use of grouping your data. You can find it in my blogpost: Understanding Join Types in Power Query - BI Gorilla

Hope that helped. If you have any more question, let me know!

Cheers, Rick 

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.