Pivot table's Filters, to deselect items when "Not all items showing"

[Rainbow_P]

Good day!


Can you please help me with a question on deselecting the items not showing in Filter?

In a pivot table, a column is used for Filters. It's a long list that "Not all items showing". I have a list of 10 items to be deselected from the Filter. How can I do that?

Not sure, but.. would Power Query or Power Pivot be good use for this case?

Thank you.

 

[RoryA]

If you have 10 items out of thousands that should be excluded, then yes, I'd probably use Power Query. If your data source is on a worksheet, you could also just add a formula column to check whether items should be included or not, then use that column as a filter.

 

[Rainbow_P]

@RoryA , thank you for the advise! Can you please share a sample of using Power Query?

​

 

[RoryA]

Is it always the same 10 items? And how familiar are you with Power Query?

 

[Rainbow_P]

@RoryA , my present question is with 10 items, and I wish to learn the way to handle them once they are changed in future.

Today's first day I get to know the term "Power Query".

 

[RoryA]

Oh, Ok then. First step, knowing how to load the data into Power Query: Import from an Excel Table - Microsoft Support

I would use two tables, one with your main data source, and the other with a list of the values you want to exclude. Load both as queries. You can then merge them using an anti-join, which excludes matching values: Left anti join - Power Query

You can either load the resulting query to a worksheet and make a pivot table off that, or just leave all the queries as connection only and you can build a pivot table using the connection (choose the external data option when creating the pivot).

If you need to alter the filter list, just adjust your table of excluded items and then refresh all.

 

[Rainbow_P]

@RoryA , my sincere gratitude for the time and patience you showed me while teaching me how to use the Power Query. Your step-by-step guidance made life beautiful!

Thank you for sharing your knowledge. Your kindness in helping a stranger means a lot to me.

 

[RoryA]

Glad we could help!

If you get stuck with Power Query, we have a Power Tools forum here for all questions related to that (and the rest of the Microsoft Power applications).