Add a pivot table to an excel file with ImportExcel powershell module

pivottable.ps1

<#
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