Skip to content

Instantly share code, notes, and snippets.

@mlhDevelopment
Forked from andyoakley/gist:1651859
Last active September 28, 2015 21:34
Show Gist options
  • Save mlhDevelopment/f84f7297131705a63191 to your computer and use it in GitHub Desktop.
Save mlhDevelopment/f84f7297131705a63191 to your computer and use it in GitHub Desktop.
Pivot Example
# Rotates a vertical set similar to an Excel PivotTable
#
# Given $data in the format:
#
# Category Activity Duration
# ------------ ------------ --------
# Management Email 1
# Management Slides 4
# Project A Email 2
# Project A Research 1
# Project B Research 3
#
# with $keep = "Category", $rotate = "Activity", $value = "Duration"
#
# Return
#
# Category Email Slides Research
# ---------- ----- ------ --------
# Management 1 4
# Project A 2 1
# Project B 3
$rotate = "Activity"
$keep = "Category"
$value = "Duration"
$pivots = $data |
select -unique $rotate |
foreach { $_.Activity}
$data |
group $keep |
foreach {
$group = $_.Group
$row = new-object psobject
$row | add-member NoteProperty $keep $_.Name
foreach ($pivot in $pivots) {
$row | add-member NoteProperty $pivot ($group | where { $_.$rotate -eq $pivot } | measure -sum $value).Sum
}
$row
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment