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 = "A-Group@company.com"
$row.MemberEmailAddress = "alex@company.com"
$dtLive.Rows.Add($Row)

$row = $dtLive.NewRow()
$row.GroupEmailAddress = "B-Group@company.com"
$row.MemberEmailAddress = "alex@company.com"
$dtLive.Rows.Add($Row)

$row = $dtLive.NewRow()
$row.GroupEmailAddress = "A-Group@company.com"
$row.MemberEmailAddress = "bob@company.com"
$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 = "A-Group@company.com"
$row.MemberEmailAddress = "alex@company.com"
$dtUpdated.Rows.Add($Row)

$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "B-Group@company.com"
$row.MemberEmailAddress = "alex@company.com"
$dtUpdated.Rows.Add($Row)

$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "A-Group@company.com"
$row.MemberEmailAddress = "bob@company.com"
$dtUpdated.Rows.Add($Row)

## NOW THE ACTUAL UPDATES
# add Bob to A-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "B-Group@company.com"
$row.MemberEmailAddress = "bob@company.com"
$dtUpdated.Rows.Add($Row)

# add new person Charlie to A-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "A-Group@company.com"
$row.MemberEmailAddress = "charlie@company.com"
$dtUpdated.Rows.Add($Row)

# add new person Dan to (new) C-Group
$row = $dtUpdated.NewRow()
$row.GroupEmailAddress = "C-Group@company.com"
$row.MemberEmailAddress = "dan@company.com"
$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
-----------------   ------------------
A-Group@company.com alex@company.com
B-Group@company.com alex@company.com
A-Group@company.com bob@company.com
B-Group@company.com bob@company.com
A-Group@company.com charlie@company.com

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
-----------------   ------------------
A-Group@company.com charlie@company.com
C-Group@company.com dan@company.com

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, Nov 04 '22 at 15:21
yes - we just want to add to the live if it's missing. thanks! — tb1, Nov 04 '22 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`](https://www.powershellgallery.com/packages/Join)/[`Join-Object Module`](https://www.powershellgallery.com/packages/JoinModule) (see also: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/45483110/1701026)): `$dtLive |FullJoin $dtUpdated -On GroupEmailAddress, MemberEmailAddress` — iRon, Nov 05 '22 at 07:21
interesting take @iRon - thanks for the module suggestion - I can definitely put that to use! — tb1, Nov 07 '22 at 13:56

score 1 · Accepted Answer · answered Nov 04 '22 at 15:55

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

B-Group@company.com -> bob@company.com
A-Group@company.com -> charlie@company.com
C-Group@company.com -> dan@company.com

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
-----------------   ------------------
A-Group@company.com alex@company.com
B-Group@company.com alex@company.com
A-Group@company.com bob@company.com
B-Group@company.com bob@company.com
A-Group@company.com charlie@company.com
C-Group@company.com dan@company.com

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, Nov 04 '22 at 20:51

PowerShell comparing two DataTables - issue with filter on two columns

1 Answers1