How to transpose rows into columns with powershell

Question

I have this input as an array name $discrepancies.

Source  Date    Store   Register Sales
XBR     8/1/2020    11741   517  1029.611
Xcenter 8/1/2020    11741   517  1047.701
XStore  8/1/2020    11741   517  1047.701
XBR     8/3/2020    11741   517  690.941
Xcenter 8/3/2020    11741   517  730.821
XStore  8/3/2020    11741   517  730.821
XBR     8/4/2020    11741   516  1027.311
Xcenter 8/4/2020    11741   516  1045.401
XStore  8/4/2020    11741   516  1045.401
XBR     8/7/2020    11741   516  688.641
Xcenter 8/7/2020    11741   516  728.521
XStore  8/7/2020    11741   516  728.521

I need to transpose the array to

Date        Store   Register    XBR         Xcenter     XStore
8/1/2020    11741   517         1029.611    1047.701    1047.701
8/3/2020    11741   517         90.941      730.821     730.821
8/4/2020    11741   516         027.311     1045.401    1045.401
8/7/2020    11741   516         688.641     728.521     728.521

I tried below codes but it works if the array has only one register.

> $discrepancies= import-csv -Path "$OuputFile\discrepancies.csv"
> 
> $pivot = @()
>     foreach ($Date in $discrepancies.Date | Select -Unique)     
>     {
>         $Props = [ordered]@{ Date = $Date }
>         foreach ($Store in $discrepancies.Store | Select -Unique) 
>         {
>             $Props += [ordered]@{ Store = $Store }
>             foreach ($Register in $discrepancies.Register | Select -Unique) 
>             {                
>                 $Props += [ordered]@{ Register = $Register }
>                 foreach ($Source in $discrepancies.Source | Select -Unique)
>                 { 
>                     $Sales = ($discrepancies.where({ $_.Source -eq $Source -and 
>                                 $_.Date -eq $Date -and $_.Store -eq $Store -and $_.Register -eq $Register})).Sales
>                     $Props += @{ $Source = $Sales }
>                 } 
>             }
>         }
>         $pivot += New-Object -TypeName PSObject -Property $Props
>     }

I received several "Item has already been added. Key in dictionary:" error message if there are multiple registers in the array. I guess error related to inserting duplicate keys into dictionary but I don't know how to correct it. Any help would be greatly appreciated. Thank you.

Answer 1

Let's try and break down the problem into two discrete steps:

1. Group discrepancies based on Date, Store and Register
2. Output an object describing the Date, Store, Register and all sources associated with that group

For the first step, we can make use of Group-Object:

$discrepancies |Group Store,Register,Date

Now that we've grouped the input correctly, we can move ahead with constructing our output:

$discrepancies |Group Store,Register,Date |ForEach-Object {
    $entries = $_.Group
    [pscustomobject]@{
        Date     = $entries[0].Date
        Store    = $entries[0].Store
        Register = $entries[0].Register
        XBR      = ($entries |Where-Object Source -eq XBR).Sales
        XCenter  = ($entries |Where-Object Source -eq XCenter).Sales
        XStore   = ($entries |Where-Object Source -eq XStore).Sales
    }
}

All entries in each group necessarily has the same Date, Store and Register value (because that's what we asked Group-Object to group on), so for those we just copy the value of the first entry in the group.