Full Anti Join (M not DAX)

[smpa01]

Hello experts,

I want to perform a full anti join. this option is not there in the user interface.

My sample data set is following

Data	Value
D1	301
D2	305
D3	330

 Table1

Data	Value
D2	305
D3	330
D5	791

Table2

 I want to achieve the following output

Data	Value
D1	301
D5	791

I am currently doing it by doing LeftAnti+RightAnti. I was wondering if I can achieve it in one step to save memory consumption.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Data"},Table2,{"Data"},"Table2",JoinKind.LeftAnti),
    #"Removed Columns2" = Table.RemoveColumns(#"Merged Queries",{"Table2"}),
    Custom1 = Table.NestedJoin(#"Changed Type",{"Data"},Table2,{"Data"},"Table2",JoinKind.RightAnti),
    #"Removed Other Columns" = Table.SelectColumns(Custom1,{"Table2"}),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Removed Other Columns", "Table2", {"Data", "Value"}, {"Data", "Value"}),
    Custom2 = #"Removed Columns2"&#"Expanded Table2"
in
    Custom2

Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}, {"Value", Int64.Type}})
in
    #"Changed Type"

Table2

Thank you in advance.

[PattemManohar · Accepted solution]

@smpa01 I've tried this way... 

let
    Source = Table.NestedJoin(Test296FullAntiJoinT1,{"Data"},Test296FullAntiJoinT2,{"Data"},"Test296FullAntiJoinT2",JoinKind.FullOuter),
    #"Expanded Test296FullAntiJoinT2" = Table.ExpandTableColumn(Source, "Test296FullAntiJoinT2", {"Data", "Value"}, {"Test296FullAntiJoinT2.Data", "Test296FullAntiJoinT2.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Test296FullAntiJoinT2", "DataNew", each if [Data] = null then [Test296FullAntiJoinT2.Data] else if [Test296FullAntiJoinT2.Data] = null then [Data] else null),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "ValueNew", each if [Value] = null then [Test296FullAntiJoinT2.Value] else if [Test296FullAntiJoinT2.Value] = null then [Value] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([DataNew] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Data", "Value", "Test296FullAntiJoinT2.Data", "Test296FullAntiJoinT2.Value"})
in
    #"Removed Columns"

[Rickmaurinus]

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.

[Anonymous · Accepted solution]

Maybe something like this could work?

• Append the two tables
• Groupby Data, and aggregating by Count Rows as well as all rows
• Filter the count to equal 1
• Expand the All Rows 
• Remove misc columns and set data types

let
    Source = Table.Combine({Table1, Table2}),
    #"Grouped Rows" = Table.Group(Source, {"Data"}, {{"Count", each Table.RowCount(_), type number}, {"Data.1", each _, type table}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 1)),
    #"Expanded Data.1" = Table.ExpandTableColumn(#"Filtered Rows", "Data.1", {"Value"}, {"Value"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Data.1",{"Count"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", Int64.Type}})
in
    #"Changed Type"

[PattemManohar · Accepted solution]

@smpa01 I've tried this way... 

let
    Source = Table.NestedJoin(Test296FullAntiJoinT1,{"Data"},Test296FullAntiJoinT2,{"Data"},"Test296FullAntiJoinT2",JoinKind.FullOuter),
    #"Expanded Test296FullAntiJoinT2" = Table.ExpandTableColumn(Source, "Test296FullAntiJoinT2", {"Data", "Value"}, {"Test296FullAntiJoinT2.Data", "Test296FullAntiJoinT2.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Test296FullAntiJoinT2", "DataNew", each if [Data] = null then [Test296FullAntiJoinT2.Data] else if [Test296FullAntiJoinT2.Data] = null then [Data] else null),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "ValueNew", each if [Value] = null then [Test296FullAntiJoinT2.Value] else if [Test296FullAntiJoinT2.Value] = null then [Value] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([DataNew] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Data", "Value", "Test296FullAntiJoinT2.Data", "Test296FullAntiJoinT2.Value"})
in
    #"Removed Columns"