Forum Discussion
Community ChampionFull Anti Join (M not DAX)
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"
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"
Helper VHi,
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.