Pivot Table Slicers mystery!!!

[adambc]

I have inherited a Workbook that has a Pivot Table with a Slicer on one Worksheet, linked to a Range in another Worksheet, which in turn is (currently, see below) a Copy/Paste from a CSV extract file from a Third Party system - I do not have access to the person who created the Pivot Table/Slicer!

One of the columns in the range is "County" in the format "FullNameOfCountynnnn" eg Hertfordshire1234, Hertfordshire4321, Buckinghamshire5678, Buckinghamshire8765, etc - the existing Slicer is linked to this column/field ...

If I create a new Slicer also based on "County", I get the Slicer List I'd expect ie the full name of all values in the column ...

But the existing Slicer, even after a refresh, has "Herts" which results in a filter of Hertfordshire1234 & Hertfordshire4321 and "Bucks" which results in a filter of Buckinghamshire5678 & Buckinghamshire8765 ...

The problem is that I have automated the Copy/Paste using Power Query, but when I change the Data Source for the Pivot Table, the original Slicer becomes disconnected losing the "custom" Slicer List ...

I have searched (and searched) for ways to customise Slicer Lists without success - can anyone help me recreate the original Slicer List?

 

[jkpieterse]

I suspect the existing slicer is tied to a different column than you think. If you right-click the slicer and choose Slicer Settings, you should see it's source column at the top of that dialog next to "Source name:".

 

[adambc]

I suspect the existing slicer is tied to a different column than you think. If you right-click the slicer and choose Slicer Settings, you should see it's source column at the top of that dialog next to "Source name:".

After a LOT of searching you are quite right!

"Herts" & "Bucks" are text grouped fields, but that's not reflected in the Slicer settings/source column (that still refers to "County").

This explains the HUGE issue that I've been asked to look at (at this stage I should explain I "work" in a voluntary role for a local charity).

The problem (as I see it) is that the text grouping can only pick up the source values to group at the time the text grouping is done eg (using the "Herts" example in my original post) if subsequent to creating the "Herts" text group field, another "County" value is used say "Hertfordshire9999", then unless "Herts" is ungrouped/regrouped with this new value included, selecting Herts in the Slicer will miss the new County value?

And I cannot see how you can possibly make this dynamic.

Unless someone has a way?

 

[jkpieterse]

I'm not sure what exactly your question is. The slicer appears to be connected to a different column than you think it is. So the solution would be to remove that slicer and add a new one connected to the correct column (field) of your table.

 

[adambc]

I'm not sure what exactly your question is. The slicer appears to be connected to a different column than you think it is. So the solution would be to remove that slicer and add a new one connected to the correct column (field) of your table.

The problem is that the column (field) that the Slicer is connected to isn't "real" - IN THE PIVOT TABLE values from the "real" column have been Grouped *** to form new fields in the Pivot Table field list ...

Using the example from my original post ...

- the new field in the Pivot Table field list as a result of the grouping ie "Herts" actually slices on "County" = Hertfordshire1234 AND Hertfordshire4321
- the new field in the Pivot Table field list as a result of the grouping ie "Bucks" actually slices on "County" = Buckinghamshire5678 AND Buckinghamshire8765

... because at the time the grouping was done, those were the only "County" values available to group ...

But we now have "County" = Hertfordshire9999 and want to include that in the "Herts" grouping - and the only way I can see to achieve that is to ungroup and regroup (because there doesn't appear to be an option to edit a text grouping) ...

Don't worry about it unless you have an immediate solution - I'm probably going to look for an alternative approach to custom Pivot Tables/Slicers now I understand what's happening ...

*** Group or ungroup data in a PivotTable - Microsoft Support. - see "Group selected items"

 

[RoryA]

the only way I can see to achieve that is to ungroup and regroup (because there doesn't appear to be an option to edit a text grouping

You can select the values in the original column that are part of the group, then ctrl+click the additional values and then group. That should add the new values to the existing group. It would be better to use an additional column in the source data for the grouping, whether using some sort of text extraction formula, or a lookup table, or power query or whatever. It's much easier to see what the groups are that way, and maintain them, in my experience.

 

[adambc]

You can select the values in the original column that are part of the group, then ctrl+click the additional values and then group. That should add the new values to the existing group. It would be better to use an additional column in the source data for the grouping, whether using some sort of text extraction formula, or a lookup table, or power query or whatever. It's much easier to see what the groups are that way, and maintain them, in my experience.

Rory

"It would be better to use an additional column in the source data for the grouping" - TOTALLY agree and what I've done in the Power Query query I've created to replace the copy/paste that the Users have currently been asked to do!!!

The Pivot Tables are unusable unless you understand how to maintain them (which few Users ever would)!!!

 

[adambc]

You can select the values in the original column that are part of the group, then ctrl+click the additional values and then group. That should add the new values to the existing group. It would be better to use an additional column in the source data for the grouping, whether using some sort of text extraction formula, or a lookup table, or power query or whatever. It's much easier to see what the groups are that way, and maintain them, in my experience.

Rory

Question to which I think the answer is No, but just in case!

The grouped text list is held in the Pivot Table Cache?

Is it possible to edit that list directly ie add/remove values manually?

Or is the only way to edit the list as you've explained (and therefore limited to only values that are present in the current data set)?

Thanks ...

 

[RoryA]

Yes it's in the cache and no you can't directly access it. That's one reason I much prefer a lookup table.

 

[adambc]

Yes it's in the cache and no you can't directly access it. That's one reason I much prefer a lookup table.

Rory

Thought so, but thanks for confirming …

Just needed to be certain before showing the problem the person who built the current Pivot Tables (a paid for Consultant!) has created - it’s come to light when a report based on the Pivot Tables was manually checked and highlighted that values that weren’t there in the data when the grouping was done were missing!

But there is a definitive table of all the possible values and their grouping which I will be using to populate a “Group” column in the data table that will flow through to the Pivot Table dynamically …

Thanks as always …

Adam