How to find out which are rows not in A table to compare table B

[Anonymous]

Hi Team,

I have two table A and B table , how we can find out which are rows not in A table to compare table B

which join we can use?

Table A	Table B
ID	ID
500 Rows	1000 Rows

[AlienSx · Accepted solution]

Table.RemoveMatchingRows(TableB, Table.ToRecords(TableA))

[shafiz_p]

@Anonymous  You could also use Table.Join and Join Kind Right Anti(All rows from right table not in left table). By using Table.Join you don't need to provide merge column name. All column will be expand automatically. You can also use Table.SelectColumns to select only columns you want for your report with MissingField.UseNull parameter, which help you to prevent failure report refresh if source column name change some how. You can say it's a safety guard.

Note: Join operation cannot result in a table with duplicate column names

Syntex are given below :

let
Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

//Join Kind - Right Anti select all rows from right table which are not in left table
//To, create a merge column, Table.NestedJoin could be used
//Since all rows or columns from first table is null, avoided nested join

MargedTbl1andTbl2 = Table.Join(Table1, "ID", Table2, "ID1",JoinKind.RightAnti),

//Selecting all the colums needed for the report, safety gaurd MissingField.UseNull
SelectColumns = Table.SelectColumns(MargedTbl1andTbl2, {"ID", "V2"},MissingField.UseNull)

in
SelectColumns

Now you have both the solution. Hope this help!!

[Keezz]

left ant merge

[aduguid]

Try this in power query 

let
    TableA = Excel.CurrentWorkbook(){[Name="TableA"]}[Content],
    TableB = Excel.CurrentWorkbook(){[Name="TableB"]}[Content],
    MergedTables = Table.NestedJoin(TableB, {"ID"}, TableA, {"ID"}, "TableA", JoinKind.LeftAnti),
    FilteredRows = Table.SelectRows(MergedTables, each [TableA] = null)
in
    FilteredRows

[Keezz]

Filtering the rows is not needed...