PowerShell- Grab all entries of the same name if any entry has a certain Property value

Question

Currently, I have a main group of items with several categories. If an entry is marked 'Y' in isEaten, I would like to pull all entries of the same FruitName into their own list, even if the other entries are marked as 'N' in the isEaten category.

List of all items:

FruitName    FruitID    isEaten    EnteredDate
---------    -------    -------    -----------
Apple          100        Y           1/2/21
Apple          101        N           2/3/21
Apple          102        N           3/4/21
Orange         103        N           4/5/21
Kiwi           104        N           5/6/21

Grabbing Data of a Fruit that was Eaten:

FruitName    FruitID    isEaten    EnteredDate
---------    -------    -------    -----------
Apple          100        Y           1/2/21
Apple          101        N           2/3/21
Apple          102        N           3/4/21

Then, I would like to remove all rows with a 'Y' and if there is more than one entry left, only keep the most recently entered date.

Final Desired Result:

FruitName    FruitID    isEaten    EnteredDate
---------    -------    -------    -----------
Apple          102        N           3/4/21

While statically removing the Apple rows might work, I am expecting this list to be periodically changing, so a dynamic solution would be best. My current approach is trying to grab all FruitNames that have a 'Y' in the isEaten category, then going back into the main list (named $all_fruits) and grabbing all FruitNames with the same name:

# Grabbing the FruitNames of isEaten fruits:
$eaten_groups= $all_fruits| ForEach-Object{$_.Group | Where-Object isEaten-eq "Y" }

# Collecting all entries in the main list $all_fruits with the same FruitNames as $eaten_groups
$eaten_all = @($all_fruits| Where-Object {
    @(Compare-Object $_ $eaten_groups-Property FruitName-IncludeEqual -ExcludeDifferent).count -eq 0
})

$sorted_eaten = $eaten_all | Group-Object -Property FruitNames| ForEach-Object{$_.Group | Sort-Object -Property EnteredDate -Descending | Select-Object -First 1}

The problem I am encountering is both properly grabbing the list since currently it is only grabbing all isEaten=Y or isEaten=N depending on whether I set eaten_groups to find Y or N.

How can I grab all entries of the same name if any entry has an isEaten = 'Y'?

Any help is greatly appreciated!

Answer 1

A streamlined version of Santiago Squarzon's helpful answer that also builds on the Group-Object cmdlet:

@'
FruitName    FruitID    isEaten    EnteredDate
Apple          100        Y           1/2/21
Apple          101        N           2/3/21
Apple          102        N           3/4/21
Orange         103        N           4/5/21
Orange         103        Y           7/5/21
Orange         103        N           8/5/21
Kiwi           104        N           5/6/21
Kiwi           104        Y           5/6/21
Kiwi           104        Y           1/6/21
'@ -replace '  +', ',' | ConvertFrom-Csv | 
  Group-Object FruitName |
    Where-Object { $_.Group.isEaten -contains 'Y' } |
      Foreach-Object {
         ($_.Group | Sort-Object -Descending { [datetime] $_.EnteredDate })[0]
      }