Forum Discussion
smpa01
Community Champion
7 years agoFull Anti Join (M not DAX)
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 Tab...
- 7 years ago
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" - Anonymous7 years ago
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"
Anonymous
7 years agoNot applicable
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"