any help is very much appreciated!
Using Powershell to merge rows and put differences in each column
Hi, I have the following table:
"EAN" | "Series"
1010 | Twilight
1010 | Sparkle
I would like to merge these based on EAN into one row with all serie information in their own column, like this:
"EAN" | "Series" | "Serie2
1010 | Twilight | Sparkle
I found a way to combine the two but cannot find a way to get the series information in separate columns:
Group-Object EAN |
Select @{n="EAN";Expression={$_.Name}},_
@{n='Serie';E={($_.Group.Serie | Sort-Object -Unique) -join ''}}|
2 answers
Sort by: Most helpful
-
-
Rich Matheisen 46,711 Reputation points
2023-04-09T18:48:43.8466667+00:00 Doing this a bit more complicated because a CSV must be rectangular. I.e., every row must have the same number of columns. In your example, simply creating the columns that have data will produce a "ragged" array of columns (uneven column widths).
# the fixed part of the column header name $ColHdr = "Data" # get the maximum number of columns needed to hold the data (minus the 'name' in the 1st column) $max = ($Groups | measure-object -Property Count -maximum).maximum # create an ordered has to hold the aggregated data ('Name' is always going to be there) $NewRow = [ordered]@{ Name = "" } foreach ($d in (1..$max)){ $NewRow[("{0}{1}" -f $ColHdr,$d)] = "" } Import-Csv C:\junk\agg.csv -Delimiter "|" | Group-Object Name | ForEach-Object{ $numdata = $_.Group.count - 1 # adjust for comparison to a zero based subscript $NewRow.Name = $_.Name # only one name per group. use as the 1st columns data for ($i=0; $i -lt $max; $i++){ if ($i -gt $numdata){ $NewRow[("{0}{1}" -f $ColHdr,($i+1))] = "" # columns left to fill, but no more data } else{ $NewRow[("{0}{1}" -f $ColHdr,($i+1))] = $_.Group.Data[$i] # fill column with data from CSV } } [PSCustomObject]$NewRow } | Sort-Object Name
The code will create output that looks like this:
Name Data1 Data2 Data3 ---- ----- ----- ----- 1010 Twilight Sparkle EAN Series Serie2 Serie3