Table value comparsion

[jerryr125]

Hi - I am looking to do the following in my Power Query workflow. I would like create a table of values not found comparing two tables.  Example:

Table 1

MS-ID	AC-ID
1	500
1	600
2	500
2	555
2	600
2	750
3	600

Table 2

MS-ID	AC-ID	Date
1	500	11/1/2024
1	500	12/1/2024
2	500	11/15/2024
2	555	1/1/2025
2	600	2/2/2025

Table 3 - Final Table

MS-ID	AC-ID
1	600
2	750
3	600

Therefore, table 3 consist of the rows in table 1 which are NOT in table 2.

Any thoughts ? 

Jerry

[jerryr125]

Thank you everyone for your help - appreciate it!

 I acheive the end result using some creative joins!

[AlienSx]

Table.RemoveMatchingRows(Table1, Table.ToRecords(Table2), {"MS-ID", "AC-ID"})

[p45cal]

I would never have thought of that combination of arguments with RemoveMatchingRows!

[Omid_Motamedise]

You can use List.Difference so, intially removed the column Date on the second table, then convert each one into a list by Table.ToRows, then use the List.Transform.

to see the preocess, just copy the next code into the advance editor

let
Table1= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1MFCK1YGwzaBsIyRxMNvUFM5GVmNuCmEbw8RjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"MS-ID" = _t, #"AC-ID" = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI1MACShob6hvpGBkYmSrE6KOJGyOJGKOpN0SRMTUESEPWmcGEzsHojfSOocCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"MS-ID" = _t, #"AC-ID" = _t, Date = _t]),
    Custom1 = List.Difference(Table.ToRows(Table1),Table.ToRows(Table.RemoveColumns(Table2,"Date"))),
    Custom2 = Table.FromRows(Custom1)

in
    Custom2

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!

[Anonymous]

Why that sounds like a LeftAnti join. 

[p45cal · Accepted solution]

Yes:

= Table.NestedJoin(Table1, {"AC-ID", "MS-ID"}, Table2, {"AC-ID", "MS-ID"}, "DeleteThis", JoinKind.LeftAnti)