1

I am working on getting some data out of CSV file with a script and have no idea to solve the most important part - I have an array with few hundred lines, there are about 50 Ids in those lines, and each Id has a few different services attached to it. Each line has a price attached.

I want to group lines by ID and Service and I want each of those groups in some sort of variable so I can sum the prices. I filter out unique IDs and Services earlier in a script because they are different all the time.

Some example data:

$data = @(
[pscustomobject]@{Id='1';Service='Service1';Propertyx=1;Price='5'}
[pscustomobject]@{Id='1';Service='Service2';Propertyx=1;Price='4'}
[pscustomobject]@{Id='2';Service='Service1';Propertyx=1;Price='17'}
[pscustomobject]@{Id='3';Service='Service1';Propertyx=1;Price='3'}
[pscustomobject]@{Id='2';Service='Service2';Propertyx=1;Price='11'}
[pscustomobject]@{Id='4';Service='Service1';Propertyx=1;Price='7'}
[pscustomobject]@{Id='2';Service='Service3';Propertyx=1;Price='5'}
[pscustomobject]@{Id='3';Service='Service2';Propertyx=1;Price='4'}
[pscustomobject]@{Id='4';Service='Service2';Propertyx=1;Price='12'}
[pscustomobject]@{Id='1';Service='Service3';Propertyx=1;Price='8'})  

$ident = $data.Id | select -unique | sort
$Serv = $data.Service | select -unique | sort

All help will be appreciated!

Mathias R. Jessen
  • 135,435
  • 9
  • 130
  • 184
Miha Žitko
  • 71
  • 4
  • 3
    If you want to show your appreciation, you might want to consider going back and accepting (or commenting on) the answers you've already received in the past, like [this one](https://stackoverflow.com/a/68288871), [this one](https://stackoverflow.com/a/68391963) or [this one](https://stackoverflow.com/a/68650031) :) – Mathias R. Jessen Aug 06 '21 at 11:40

1 Answers1

4

Use Group-Object to group objects by common values across one or more properties.

For example, to calculate the sum per Id, do:

$data |Group-Object Id |ForEach-Object {
  [pscustomobject]@{
    Id  = $_.Name
    Sum = $_.Group |Measure-Object Price -Sum |ForEach-Object Sum
  }
}

Which should yield output like:

Id Sum
-- ---
1   17
2   33
3    7
4   19
Mathias R. Jessen
  • 135,435
  • 9
  • 130
  • 184