Forum Discussion
Table value comparsion
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
Yes:
= Table.NestedJoin(Table1, {"AC-ID", "MS-ID"}, Table2, {"AC-ID", "MS-ID"}, "DeleteThis", JoinKind.LeftAnti)
6 Replies
Table.RemoveMatchingRows(Table1, Table.ToRecords(Table2), {"MS-ID", "AC-ID"})I would never have thought of that combination of arguments with RemoveMatchingRows!
Yes:
= Table.NestedJoin(Table1, {"AC-ID", "MS-ID"}, Table2, {"AC-ID", "MS-ID"}, "DeleteThis", JoinKind.LeftAnti)
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 Custom2If 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!
Thank you everyone for your help - appreciate it!
I acheive the end result using some creative joins!
