How to summarize value rows of one column with reference to another column in PowerShell object-CodePudding

I'm learning to work with the import-excel module and have successfully imported the data from a sample.xlsx file. I need to extract out the total amount based on the values of another column values. Basically, I want to just create a grouped data view where I can store the sum of values next to each type. Here's the sample data view.

Type        Amount
level 1     $1.00
level 1     $2.00
level 2     $3.00
level 3     $4.00
level 3     $5.00

Now to import I'm just using the simple code

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

#extracting distinct type values
$distinctTypes = $importedExcelRows | Select-Object -ExpandProperty "Type" -Unique

#looping through distinct types and storing it in the output
$output = foreach ($type in $distinctTypes) 
   {
        $data | Group-Object $type | %{
        New-Object psobject -Property @{
            Type = $_.Name
            Amt = ($_.Group | Measure-Object 'Amount' -Sum).Sum
        }
    }
}

$output

The output I'm looking for looks somewhat like:

Type      Amount
level 1   $3.00
level 2   $3.00
level 3   $9.00

However, I'm getting nothing in the output. It's $null I think. Any help is appreciated I think I'm missing something in the looping.

CodePudding user response：

I don't know what your issue is but when the dollar signs are not part of the data you pull from the Excel sheet it should work as expected ...

$InputCsvData = @'
Type,Amount
level 1,1.00
level 1,2.00
level 2,3.00
level 3,4.00
level 3,5.00
'@ |
ConvertFrom-Csv

$InputCsvData |
Group-Object -Property Type |
ForEach-Object {
    [PSCustomObject]@{
        Type = $_.Name
        Amt  = '${0:n2}'-f ($_.Group | Measure-Object -Property Amount -Sum).Sum
    }
}

The ouptut looks like this:

Type    Amt  
----    ---
level 1 $3,00
level 2 $3,00
level 3 $9,00

Otherwise you may remove the dollar signs before you try to summarize the numbers.

CodePudding user response：

You're halfway there by using Group-Object for this scenario, kudos on that part. Luckily, you can group by the type at your import and then measure the sum:

$fileName = "C:\SampleData.xlsx"
Import-Excel -Path $fileName | Group-Object -Property Type | % {
    $group = $_.Group | % { 
        $_.Amount = $_.Amount -replace '[^0-9.]'
        $_
    } | Measure-Object -Property Amount -Sum 
    [pscustomobject]@{
        Type = $_.Name
        Amount = "{0:C2}" -f $group.Sum
    }
}

Since you can't measure the amount in currency format, you can remove the dollar sign with some regex of [^0-9.], removing everything that is not a number, or ., or you could use ^\$ instead as well. This allows for the measurement of the amount and you can just format the amount back to currency format using the string format operator '{0:C2} -f ....