PowerShell comparing two DataTables - issue with filter on two columns

Question

Issue: When I try compare two data tables against two columns (show where column A=column A, column B is not present), I can't seem to get a match.

[Sample data below]

How can I match [only the groups that both tables have in common] and show [who is missing from the matched groups]? When I try, I can only show who is missing from any of the groups (aka Charlie). I also need Bob to appear in group B.

To reproduce the issue - run this snippet in PowerShell:

## Live data table
# create "live" data:
$dtLive = New-Object System.Data.DataTable
$dtLive.Columns.Add("GroupEmailAddress") 
$dtLive.Columns.Add("MemberEmailAddress")

# add Alex to both groups, add Bob to just A-Group
$row = $dtLive.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtLive.Rows.Add($Row)

$row = $dtLive.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtLive.Rows.Add($Row)

$row = $dtLive.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtLive.Rows.Add($Row)

## Updated data table
# mimic existing "live" data:
$dtUpdated = New-Object System.Data.DataTable
$dtUpdated.Columns.Add("GroupEmailAddress") 
$dtUpdated.Columns.Add("MemberEmailAddress")

# add Alex to both groups, add Bob to just A-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

## NOW THE ACTUAL UPDATES
# add Bob to A-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

# add new person Charlie to A-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

# add new person Dan to (new) C-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "[email protected]"
$row.MemberEmailAddress = "[email protected]"
$dtUpdated.Rows.Add($Row)

This snippet shows any entries that share the same group names:

$dtUpdated | Where-Object {($_.GroupEmailAddress -in $dtLive.GroupEmailAddress)}

results:

GroupEmailAddress   MemberEmailAddress
-----------------   ------------------
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]

This snippet shows ONLY Member email addresses that are missing in the LIVE data table (irrespective of group membership):

$dtUpdated | Where-Object {($_.MemberEmailAddress -notin $dtLive.MemberEmailAddress)}

result:

GroupEmailAddress   MemberEmailAddress
-----------------   ------------------
[email protected] [email protected]
[email protected] [email protected]

Since I want to match on groups for what is missing, we don't want Dan (group C is not in the live table). We want Charlie (group A) and Bob (group B)

If I run something like this:

$dtUpdated | Where-Object {($_.GroupEmailAddress -in $dtLive.GroupEmailAddress) -and ($_.MemberEmailAddress -notin $dtLive.MemberEmailAddress)}

or

$dtUpdated | Where-Object {($_.GroupEmailAddress -in $dtLive.GroupEmailAddress)} | Where-Object {($_.MemberEmailAddress -notin $dtLive.MemberEmailAddress)}

I end up filtering to just Charlie (new to any group). How do I include that Bob in group B is missing?

Thanks!!

Basically the premise would be to add those missing rows to $dtLive right? — Santiago Squarzon
– Santiago Squarzon, Commented Nov 4, 2022 at 15:21
yes - we just want to add to the live if it's missing. thanks! — tb1
– tb1, Commented Nov 4, 2022 at 15:35
Apparently you want to do a FullJoin. For a complete reusable solution which is probably faster as it uses a hashtable, you might try this Join-Object script/Join-Object Module (see also: In Powershell, what's the best way to join two tables into one?): $dtLive |FullJoin $dtUpdated -On GroupEmailAddress, MemberEmailAddress — iRon
– iRon, Commented Nov 5, 2022 at 7:21
interesting take @iRon - thanks for the module suggestion - I can definitely put that to use! — tb1
– tb1, Commented Nov 7, 2022 at 13:56

Santiago Squarzon · Accepted Answer · 2022-11-04 15:55:55Z

1

If I understand correctly this should work, basically the new rows added to $dtLive using the examples tables would be:

[email protected] -> [email protected]
[email protected] -> [email protected]
[email protected] -> [email protected]

The code would be:

$map = $dtLive.Rows | Group-Object GroupEmailAddress -AsHashTable -AsString
foreach($row in $dtUpdated.Rows) {
    # if this `GroupEmailAddress` doesn't exist in `$dtLive`
    if($row.GroupEmailAddress -notin $dtLive.GroupEmailAddress) {
        $dtLive.ImportRow($row)
        continue
    }
    # if this `MemberEmailAddress` doesn't exist in the Group Of `GroupEmailAddress`
    if($row.MemberEmailAddress -notin $map[$row.GroupEmailAddress].MemberEmailAddress) {
        $dtLive.ImportRow($row)
    }
}

And the final output when inspecting $dtLive:

GroupEmailAddress   MemberEmailAddress
-----------------   ------------------
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]
[email protected] [email protected]

answered Nov 4, 2022 at 15:55

Santiago Squarzon

65.9k5 gold badges26 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tb1 Over a year ago

Oh my gosh - that's AWESOME. I don't need to add 'Dan' per my original scope (only looking at missing members of groups that match), but that's irrelevant: That hashtable method is what I need to do the full compare cleanly. This is a great answer and will help me in future work. Thanks so much!

tb1 Over a year ago

#liveSaver... :D

Collectives™ on Stack Overflow

PowerShell comparing two DataTables - issue with filter on two columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related