PivotTable and Table Filters

[CraigChamberlain]

Hi,

There is a nice example of the Pivot Table Filters here and it a Table comes with AutoFilters. https://github.com/dfinke/ImportExcel/blob/master/Examples/PivotTableFilters/testPivotFilter.ps1

Can you preset a Filter value though? Might look a bit like this.

$data =@"
Region,Area,Product,Units,Cost
North,A1,Apple,100,.5
South,A2,Pear,120,1.5
East,A3,Grape,140,2.5
West,A4,Banana,160,3.5
North,A1,Pear,120,1.5
North,A1,Grape,140,2.5
"@ | ConvertFrom-Csv

$data |
    Export-Excel $xlFile -Show `
        -AutoSize -AutoFilter `
        -IncludePivotTable `
        -PivotRows Product `
        -PivotData @{"Units"="sum"} -PivotFilter @{Region = "North"; Area = "All"} -Activate

[dfinke]

What does -PivotFilter do now for you?

[CraigChamberlain]

It provides the option to filter once Im in the file. I would like to preset the filter to a specific value.

On Tue, 14 Nov 2023, 18:18 Doug Finke, @.***> wrote:

What does -PivotFilter do now for you?

— Reply to this email directly, view it on GitHub https://github.com/dfinke/ImportExcel/issues/1538#issuecomment-1810871188, or unsubscribe https://github.com/notifications/unsubscribe-auth/ALRDEQ3WH7HZNXFNA3KMGWTYEOYWZAVCNFSM6AAAAAA7JB7MO6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMJQHA3TCMJYHA . You are receiving this because you authored the thread.Message ID: @.***>

[dfinke]

Yes, the Pivote Table Filter does not have the capability to be preset when the file is created

[CraigChamberlain]

I can't see a property for this in the XML either. Do you know if it can be done to an existing pivot table? I couldn't find any likely properties in PageFields . There is a property, Items, that is only populated once the file has been opened and saved.

#testPivotFilter.ps1
$excel = Open-ExcelPackage $xlFile
$excel.Sheet1PivotTable.PivotTables[0].PageFields[0]