How to add nested grouping of PSObject Properties based imported values of an excel file?-CodePudding

With reference to the post (How to summarize value rows of one column with reference to another column in PowerShell object), I'm moving forward with working around nested group-objects by adding more fields into input file in .xlsx format. I want to group the property values by date, type, mode, and sum(Amount).

However, neither the input nor the output print anything, and neither shows an error message. Here's the sample data in my Excel file:

Date      Type        Amount    Mode
1-1-2021  level 1     $1.00     AA
1-1-2021  level 1     $2.00     AA
1-2-2021  level 2     $3.00     AAAA
1-2-2021  level 3     $4.00     AAAA
1-3-2021  level 3     $5.00     AAA
1-3-2021  level 3     $2.00     AAA

I tried adding nested group objects into the groups of different properties, and in the innermost code block I tried to fill the PSCustomObject with the names of all 3 groups along with the sum of the number field. When I put a breakpoint, I could see the values being filled in the $variables when I hovered the mouse. However, when I return the object at the end of the nested grouping and loops it prints nothing. Also, I feel like I'm using more than required number of loops can anyone please provide any feedback on problems like this? Here's my progress so far:

$fileName = "C:\SampleData.xlsx"
$data = Import-Excel -Path $fileName

$summary  = $data
| Group-Object -Property $('Date') | ForEach-Object {
    $dateObj = $_.Name
    $dateGrp = $_.Group | ForEach-Object {
        $_ | Group-Object -Property $('Type') | ForEach-Object {
            $typeObj = $_.Name
            $typeGrp = $_.Group | ForEach-Object {
                $_ | Group-Object -Property $('Mode') | ForEach-Object {
                    $modeGrp = $_.Group | ForEach-Object {
                        $_.$('Amount') = $_.$('Amount') -replace '[^0-9.]'
                        $_
                    } | Measure-Object -Property $('Amount') -Sum
                    [pscustomobject]@{
                        'Date; = $dateObj # prints date
                        'Type' = $typeObj # prints type
                        'Mode' =  $_.Name # prints Mode
                        'Amount' = "{0:C2}" -f $modeGrp.Sum # summed up value of amount
                    }
                }
            }
        }
    }   
}   
$summary

Desired Output

 Date      Type       Amount    Mode
1-1-2021  level 1     $3.00     AA
1-2-2021  level 2     $3.00     AAAA  
1-2-2021  level 3     $4.00     AAAA
1-3-2021  level 3     $7.00     AAA

CodePudding user response：

The code as simplified as possible I believe could be like this (the following instead of creating new objects, will update the first object in each group):

# group by date first
$result = Import-Excel -Path $fileName | Group-Object Date | ForEach-Object {
    # group by Type each group
    $_.Group | Group-Object Type | ForEach-Object {
        # group by Mode each group
        $_.Group | Group-Object Mode | ForEach-Object {
            # sum the Amount of all objects in this group
            $sum = [System.Linq.Enumerable]::Sum([double[]] $_.Group.Amount.TrimStart('$'))
            # update the Amount property of the first object
            $_.Group[0].Amount = $sum.ToString('C2')
            # ouput the first object
            $_.Group[0]
        }
    }
}

$result | Format-Table

Using the test data as presented:

Date     Type    Amount Mode
----     ----    ------ ----
1-1-2021 level 1 $3.00  AA
1-2-2021 level 2 $3.00  AAAA
1-2-2021 level 3 $4.00  AAAA
1-3-2021 level 3 $7.00  AAA