Skip to main content

Simple PivotTables in PowerShell

Quick script for PivotTable-like functionality in PowerShell. I find myself using this a lot.

[sourcecode language="powershell"]

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 } [/sourcecode]