Skip to content

Instantly share code, notes, and snippets.

@andyoakley

andyoakley/gist:1651859

Created January 21, 2012 07:10
Show Gist options
  • Save andyoakley/1651859 to your computer and use it in GitHub Desktop.
Save andyoakley/1651859 to your computer and use it in GitHub Desktop.
Download ZIP
Simple PivotTable in Powershell
Raw
gistfile1.txt
# 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
}
@DennisL68
Copy link

Converted the script to an Advanced Function

function Invoke-Pivot {

param (
    $data,
    [string]$rotate, # Bits along the top
    [string]$keep, # Those along the side
    [string]$value # What to total
)

# Create variable to store the output
$NewRows = @()

# Fields of interest
# $rotate = "Activity" # Bits along the top
# $keep = "Category" # Those along the side
# $value = "Duration" # What to total

# Find the unique "Rotate" [top row of the pivot] values and sort ascending
$pivots = $data | select -unique $rotate | foreach { $_.$rotate} | Sort-Object

# Step through the original data...
#  for each of the "Keep" [left hand side] find the Sum of the "Value" for each "Rotate"

$data | 
    group $keep | 
    foreach { 
        $group = $_.Group 
        # Create the data row and name it as per the "Keep"
        $row = new-object psobject
        $row | add-member NoteProperty $keep $_.Name 
        
        foreach ($pivot in $pivots) { # Cycle through the unique "Rotate" values and get the sum
            $row | add-member NoteProperty $pivot ($group | where { $_.$rotate -eq $pivot } | measure -sum $value).Sum
        }

        # Add the total to the row
        $row | add-member NoteProperty Total ($group | measure -sum $value).Sum

        # Add the row to the collection 
        $NewRows += $row
    } 

# Do something with the pivot rows
return $NewRows

<#
.SYNOPSIS
Transforms rows and columns of a PSObject or other named object.

.DESCRIPTION
Long description

.EXAMPLE
PS C:>
Explanation of what the example does

.INPUTS
Inputs (if any)

.OUTPUTS
Output (if any)

.NOTES
#############################################################################
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 Total 
---------- ----- ------ -----
Management     1      4     5
Project A      2      1     3
Project B      3            3

#############################################################################
Changes
========# 
Who          When       Details
------------ ---------- -----------------------------------------------
dennis      2019-10-22  Converted to advanced script

aphalon 2015-07-23 1. Added $NewRows variable to hold the output
2. Unique values were hardcoded for the example
-> changed to be the variable fields
3. Sorted $pivots
4. Added a Total column
5. Commented it a little :)
#############################################################################
#>
}

@lachlann562
Copy link

lachlann562 commented Nov 30, 2023 •
edited
Loading

I think i found another version that was enhanced from above, it was intended to support multiple fields but i found several problems. My final version supports multiple row and column fields, handles null values and also supports pipeline input. Unfortunately i can't credit the original author as i can't find that original post which I enhanced.

function Get-ConcatProperties
{
	<#
	.SYNOPSIS
	Conatenates the properties of an object
	.PARAMETER obj
	Object to process
	.PARAMETER Property
	Properties to concatenate
	.PARAMETER delim
	Delimiter to use when concatenating properties
	.OUTPUTS
	concatenate string using the value of the provided properties
	e.g. if Property=@('a','b') and input obj is [PSCustomObject]@{a=3,b=4} the delimited output would be 3,4

	e.g. if Property=@('a','c','d') and input obj is [PSCustomObject]@{a=3,b=4,d=6} the delimited output would be 3,,6
	#>
	param(
		[Parameter(ValueFromPipeline=$true,Mandatory=$true)]
		[psobject[]]$obj,
		[Parameter(Mandatory=$false)]
		[string]$delim = ",",
		[Parameter(Mandatory=$true)]
		[string[]]$Property = ",",
        	[string]$NullValue
	)

	process {
		foreach ($o in $obj)
		{
			$o | ForEach-Object { 
				$members = @(); 
				foreach ($p in $Property) 
				{ 
					if ($null -eq $obj.$p ) 
					{ 
                        if ($NullValue) {
                            $members += $nullValue
                        } else {
						    $members += "" 
                        }
					} elseif ("" -eq $obj.$p) {
                        if ($NullValue) {
                            $members += $nullValue
                        } else {
						    $members += "" 
                        }
                    } else {
						$members += $obj.$p
					} 
					write-output ([string]::Join(",",$members))
				}
			}
		}
	}
	
}#function Get-ConcatProperties

function Get-PivotedData
{
    [CmdletBinding(PositionalBinding=$false)]
	<#
	.SYNOPSIS
	Pivots the data given a row and column field
	.PARAMETER data
	The data to pivot
	.PARAMETER rowfield
	Which object property to use as the label/grouping of each row in the output table
	.PARAMETER colfield
	Which object property to use as the label/grouping of each column in the output table
	.PARAMETER colfieldNullLabel
	.PARAMETER totalfield
	Which object property to use for each cell in the output table, this field will be totaled using the method specified in TotalMethod
	.PARAMETER TotalMethod
	How should the cell be populated using the totalField (e.g. Sum)
	.PARAMETER RowcolDelim
	If multiple proeprties are specified for row/column, how should they be concatenated in the output table
	e.g. if colfield=@('a','b') and input obj is [PSCustomObject]@{a=3,b=4} the delimited output would be 3,4
	.PARAMETER ShowRowTotal
	If true it will show the "totals" column at the end  of each "row"
	.PARAMETER ShowColumnTotal
	If true it will show the "totals" row at the end of each "column"
	.EXAMPLE
    @(
        [pscustomobject]@{a='a_data';b='b_data';c=1; d='d_data';e='e_data'}
        ,[pscustomobject]@{a='a_data';b='b_data';c=2; d=''}
        ,[pscustomobject]@{a='a_data';b='';c=3; d=''}
        ,[pscustomobject]@{a='a_data';b='';c=3; d='d_data2'}
    ) | Get-PivotedData -rowfield a,b -colfield d,e -colfieldNullLabel "NoData" -totalfield c -totalmethod Sum -ShowRowTotal -ShowColumnTotal

    Result:
    a         b         NoData,NoData d_data,e_data d_data2,NoData Total Sum
    -         -         ------------- ------------- -------------- ---------
    a_data    b_data                2             1                        3
    a_data                          3               3                      6
    Total Sum Total Sum             5             1 3                      9

	#>
    param(
		[Parameter(Mandatory=$true,Position=0,ValueFromPipeline=$true)]
		[psobject[]]$data,
		[Parameter(Mandatory=$true)]
		[string[]]$rowfield,
		[Parameter(Mandatory=$true)]
		[string[]]$colfield,
		[string]$colfieldNullLabel,
		[Parameter(Mandatory=$false)]
		[string]$RowcolDelim=',',
		[Parameter(Mandatory=$true)]
        [string]$totalfield,
		[Parameter(Mandatory=$false)]
		[ValidateSet("Sum","Average","Maximum","Minimum","Count")]
		[string]$totalmethod="sum",
        [switch]$ShowColumnTotal,
        [switch]$ShowRowTotal
    )
    begin {
        $rows = @()

        # Fields of interest
        #$colfield  # Bits along the top
        #$rowfield    # Those along the side
        #$totalfield # What to total
        $dataToProc = @()
    }
    process {
        foreach ($d in $data)
        {
            $dataToProc += $d
        }
    }
    end {
        if (-not $colfieldNullLabel) {
            $colfieldNullLabel = "(null $colfield)"
        }
        #get unique list of properties in the colfield list that actually exist in the data
	    $pivots = $dataToProc | Sort-Object -Property $colfield -Unique | select -Property $colfield  | select *, @{n='__!__Label';e={$_| Get-ConcatProperties -Property $colfield -delim $RowcolDelim -NullValue $colfieldNullLabel | select -last 1}}
        

        $MeasureParms = @{
            Property=$totalfield
        }
        if ($totalmethod -ne 'Count') {
            $MeasureParms[$totalmethod]=$true
        }

        
        # Step through the original data...
        $dataToProc | 
        #   Group by the rowfield properties
            Group-Object $rowfield | 
        #   for each of the "rowfield" groups [left hand side] find the Sum (or other totalmethod) of the "totalfield" for each "rowfield" grouping
            Foreach-Object { 
                $group = $_.Group 
                # Create the data "row" and name it as per the "rowfield" set
                #$row = new-object psobject
                
                #the heading is the list of grouping row property names, the value is the unique 'group' in the data itself
                $row = $_.group | Select-Object -Property $rowfield -First 1
                # Cycle through the unique "rowfield" property groups values and get the total
                for ($i = 0; $i -lt $pivots.count; $i++)
                {
                    $pivot=$pivots[$i]
                    $pivotLabel = $pivots[$i]."__!__Label"
                    
                    $Values = $group | Where-Object {
                            foreach ($f in $colfield) {
                                if (-not ($_.$f -eq $pivot.$f)) {
                                    return $false;
                                }
                            } return $true;
                          }


                    $row | add-member -MemberType NoteProperty -Name $pivotLabel -Value ($Values | Measure-Object @MeasureParms).$totalmethod
                    
                }
                # Add the total to the row
                if ($ShowRowTotal)
                {
                
                    $row | add-member NoteProperty "Total $totalmethod" ($group  | Measure-Object @MeasureParms).$totalmethod
                }
                # Add the row to the collection 
                $rows += $row
            }#each row 

        #if total for each column is desired
        if ($ShowColumnTotal)
        {
            
            $ColTotalHash = [ordered]@{}
            foreach ($f in $rowfield) {
                $ColTotalHash[$f]="Total $totalmethod"
            }
            $ColTotalRow=[pscustomobject]$ColTotalHash
                
            #$ColTotalRow | add-member NoteProperty ([string]::Join($RowcolDelim, $rowfield)) "Total $totalmethod"
                
            $MeasureParms.Remove("property") #remove the property, we don't need it for the total
            foreach ($pivot in $pivots | select -expandProperty "__!__Label")
            {
                #$ColTotalRow | add-member NoteProperty ([string]::Join($RowcolDelim, $keep)) "_Total $totalmethod"
                
                $ColTotalRow | add-member NoteProperty -Name $pivot -Value ($rows.$pivot | Measure-Object @MeasureParms ).$totalmethod
                # Add the total to the row
            }

            #show the grand total of both row & column
            if ($ShowRowTotal)
            {
                $ColTotalRow | add-member NoteProperty -Name "Total $totalmethod" -Value ($rows."Total $totalmethod" | Measure-Object @MeasureParms).$totalmethod
            }

            $rows += $ColTotalRow


        }
        Write-Output $rows
    }#end block
}#function Get-PivotedData

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment