Powershell Split PSCustomObject if Property has multiple values while duplicating other values

Question

My current PSCustomObject

Invoice	Auction	Item ID	Date
18585208	X8C3E5651	K5W6L07795	Feb 18, 2021
99457377	U9V7E3466	J2X1D40777	Feb 14, 2022
91833688	D2A7O0545	T7Z6Y74627,H3B4U81837	Feb 23, 2021
76058226	K3N0T6688	G1D0B37486,Q3B8Z87471	Aug 21, 2021
65314754	C1C1F6626	Q8D2J82095	Dec 9, 2021

Rows 3 and 4 have multiple Item ID's which need to be split into their own rows but with the Invoice, Auction, and Date properties duplicated to match.

Intended output:

Invoice	Auction	Item ID	Date
18585208	X8C3E5651	K5W6L07795	Feb 18, 2021
99457377	U9V7E3466	J2X1D40777	Feb 14, 2022
91833688	D2A7O0545	T7Z6Y74627	Feb 23, 2021
91833688	D2A7O0545	H3B4U81837	Feb 23, 2021
76058226	K3N0T6688	G1D0B37486	Aug 21, 2021
76058226	K3N0T6688	Q3B8Z87471	Aug 21, 2021
65314754	C1C1F6626	Q8D2J82095	Dec 9, 2021

After a ton of research, I've tried multiple things but the logic is getting lost on me. I can select by property name and split each but have trouble on the new line portion.

My Object code:

$data = foreach ($foo in $AllMatches)
{
    [PSCustomObject]@{
        'Invoice' = ($data.Groups.Where{$_.Name -like 'Invoice'}).Value
        'Auction' = ($data.Groups.Where{$_.Name -like 'Auction'}).Value
        'Items' = ($data.Groups.Where{$_.Name -like 'Items'}).Value 
        'Date' = ($data.Groups.Where{$_.Name -like 'Date'}).Value
    }
}

score 2 · Answer 1 · answered Feb 14 '22 at 12:27

Your code is a bit unclear, but if you already have an array of PsObjects and need to split on a certain property, here's one way of doing that.

For demo, I'm using your example data as Here-String and convert that from CSV:

$dataToSplit = @"
Invoice Auction Item ID Date
18585208    X8C3E5651   K5W6L07795  Feb 18, 2021
99457377    U9V7E3466   J2X1D40777  Feb 14, 2022
91833688    D2A7O0545   T7Z6Y74627,H3B4U81837   Feb 23, 2021
76058226    K3N0T6688   G1D0B37486,Q3B8Z87471   Aug 21, 2021
65314754    C1C1F6626   Q8D2J82095  Dec 9, 2021
"@ | ConvertFrom-Csv -Delimiter "`t"

$data = foreach ($item in $dataToSplit) {
    $item.'Item ID' -split ',' | ForEach-Object {
        [PSCustomObject]@{
            'Invoice' = $item.Invoice
            'Auction' = $item.Auction
            'Item ID' = $_
            'Date'    = $item.Date
        }
    }
}

$data

Output:

Invoice  Auction   Item ID    Date        
-------  -------   -------    ----        
18585208 X8C3E5651 K5W6L07795 Feb 18, 2021
99457377 U9V7E3466 J2X1D40777 Feb 14, 2022
91833688 D2A7O0545 T7Z6Y74627 Feb 23, 2021
91833688 D2A7O0545 H3B4U81837 Feb 23, 2021
76058226 K3N0T6688 G1D0B37486 Aug 21, 2021
76058226 K3N0T6688 Q3B8Z87471 Aug 21, 2021
65314754 C1C1F6626 Q8D2J82095 Dec 9, 2021

score 1 · Answer 2 · answered Feb 14 '22 at 12:28

Here you have an example of how you can do it, the following iterates over each object of the array and splits by comma the value of Item ID, if result is one elements it returns the element as is, else, it will iterate over the elements of splitting and update a copy of the row the current row.

foreach($obj in $array) {
    if(($items = $obj.'Item ID'.Split(',')).Count -eq 1) {
        $obj
        continue
    }
    foreach($item in $items) {
        $i = $obj.PSObject.Copy()
        $i.'Item ID' = $item
        $i
    }
}

Powershell Split PSCustomObject if Property has multiple values while duplicating other values

2 Answers2