Forum Discussion
jerryr125
Helper IV
1 year agoTable 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...
- 1 year ago
Yes:
= Table.NestedJoin(Table1, {"AC-ID", "MS-ID"}, Table2, {"AC-ID", "MS-ID"}, "DeleteThis", JoinKind.LeftAnti)
Omid_Motamedise
Super User
1 year agoYou 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!