Oct 22 2020 04:02 AM
Hello
I have an Excel file with PivotTable Fields. In one of the fields it has 50 filters that can be selected. I only want 5 of these filters to work with. What I would like to do is select those 5 filters and then merge/combine them into 1 new filter. Does anyone know how I can do this?
Thanks in advance
Oct 23 2020 03:35 AM - edited Oct 23 2020 03:36 AM
Do I understand you have a Column with 50 unique items in it and you'd like to group those into a single item that you can then use to filter a Pivot Table?
If you're using Power Pivot then:
If that's the case then using a mapping table to group the 5 items into the 1 item and the 45 items into "other" could work, and then either use that as a Table with a Relationship in the model or use Power Query to merge the new table definitions onto the the side of the existing table (option 2 better if your 50 item table is currently being used as a lookup table rather than the fact table)
Wyn
[MVP]
Oct 23 2020 03:37 AM
Oct 26 2020 05:58 AM
Thanks for your response. I already have a Pivot Table that was sent to me, that has pre-set filters. Under one of the filters is 50 categories, I want to combine 5 of those 50 into a single category. I'm unsure how to use a mapping table, any help would be great. Thanks
Oct 27 2020 05:16 PM
I'm not quite clear on what you mean, but you may be able to Ctrl select multiple items and group them and then use them as a filter