Examples/PivotTable/MultiplePivotTables.ps1
|
$data = ConvertFrom-Csv @"
Region,Date,Fruit,Sold North,1/1/2017,Pears,50 South,1/1/2017,Pears,150 East,4/1/2017,Grapes,100 West,7/1/2017,Bananas,150 South,10/1/2017,Apples,200 North,1/1/2018,Pears,100 East,4/1/2018,Grapes,200 West,7/1/2018,Bananas,300 South,10/1/2018,Apples,400 "@ | Select-Object -Property Region, @{n = "Date"; e = {[datetime]::ParseExact($_.Date, "M/d/yyyy", (Get-Culture))}}, Fruit, Sold $xlfile = "$env:temp\multiplePivotTables.xlsx" Remove-Item $xlfile -ErrorAction SilentlyContinue $excel = $data | Export-Excel $xlfile -PassThru -AutoSize -TableName FruitData $pivotTableParams = @{ PivotTableName = "ByRegion" Address = $excel.Sheet1.cells["F1"] SourceWorkSheet = $excel.Sheet1 PivotRows = @("Region", "Fruit", "Date") PivotData = @{'sold' = 'sum'} PivotTableStyle = 'Light21' GroupDateRow = "Date" GroupDatePart = @("Years", "Quarters") } $pt = Add-PivotTable @pivotTableParams -PassThru #$pt.RowHeaderCaption ="By Region,Fruit,Date" $pt.RowHeaderCaption = "By " + ($pivotTableParams.PivotRows -join ",") $pivotTableParams.PivotTableName = "ByFruit" $pivotTableParams.Address = $excel.Sheet1.cells["J1"] $pivotTableParams.PivotRows = @("Fruit", "Region", "Date") $pt = Add-PivotTable @pivotTableParams -PassThru $pt.RowHeaderCaption = "By Fruit,Region" $pivotTableParams.PivotTableName = "ByDate" $pivotTableParams.Address = $excel.Sheet1.cells["N1"] $pivotTableParams.PivotRows = @("Date", "Region", "Fruit") $pt = Add-PivotTable @pivotTableParams -PassThru $pt.RowHeaderCaption = "By Date,Region,Fruit" $pivotTableParams.PivotTableName = "ByYears" $pivotTableParams.Address = $excel.Sheet1.cells["S1"] $pivotTableParams.GroupDatePart = "Years" $pt = Add-PivotTable @pivotTableParams -PassThru $pt.RowHeaderCaption = "By Years,Region" Close-ExcelPackage $excel -Show |