I’m currently working on a PowerShell script that leverages the ImportExcel PowerShell module to create an Excel spreadsheet with data, then creating a pivot table from that data. I had manually created a sheet and pivot table manually from a report from a print management system that I manage, and I’d like to automate this task.
I’m able to replicate the bulk of the manual work of creating the worksheet and the pivot table with the following, where $baseReportInfo contains the report data:
$baseReportInfo | Export-Excel -Path .test_04_053124.xlsx -WorkSheetName $sheetName -ClearSheet -IncludePivotTable -PivotRows "billing_department","device" -PivotData @{"Host name"="Count"} -PivotFilter "Data Source" -PivotColumns "Device Type"
The only item I haven’t been able to figure out is how to group two of the “Device Type” columns in the pivot table. I’m able to do so manually in Excel, where I highlight the “HP (FutureSmart)” and “HP FutureSmart (Legacy)” column headers, go to Data->Group to create the grouping, then change the title of the grouping to “HP”:
I did some initial searching and looking through some script examples on the ImportExcel Github page, and didn’t see anything that stood out to me as a way to accomplish this. Is is possible to perform this grouping task in PowerShell via the ImportExcel module?
Thanks in advance, if you need any other details, please let me know.