Skip to content

Instantly share code, notes, and snippets.

@fatherjack
Created July 7, 2021 07:33
Show Gist options
  • Save fatherjack/37d9aed940c493491f5f9a3a06ddfd1c to your computer and use it in GitHub Desktop.
Save fatherjack/37d9aed940c493491f5f9a3a06ddfd1c to your computer and use it in GitHub Desktop.
Add a pivot table to an excel file with ImportExcel powershell module
<#
Assumptions
$splatExcel is a hash table used for splatting the data into the Excel file with Export-Excel. Path is the path for the file.
The Excel file has a worksheet with the name of _BatchLoadData_ which has a single table named _BatchLoadDataTable_ in it
The data table has many columns that include _JobID_ and _Batch_
Result
A worksheet is added to the workbook called _BatchLoadPvt_ which contains the pivot table that we describe
#>
# add a pivot table for the data load comparisons
$Excel = Open-ExcelPackage -path $splatExcel.path -KillExcel
$PivotSplat = @{
ExcelPackage = $Excel
PivotRows = 'JobID'
PivotColumns = 'Batch'
PivotData = @{ step_Duration_mins = 'sum' }
SourceWorksheet = 'BatchLoadData'
SourceRange = $Excel.BatchLoadData.Tables.address.address # 'BatchLoadTable'
PivotTableName = 'BatchLoadPvt'
NoTotalsInPivot = $true
}
Add-PivotTable @PivotSplat
Close-ExcelPackage $Excel
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment