255 character limit in Pivot Table cell

[adambc]

I have a table, "AllGroupMembers", with 2 columns ...

- column A = GroupName
- column B = MemberName

For each MemberName there are multiple rows depending on how many GroupName(s) they belong to - I need to create a list with unique GroupName rows in column A, with all MemberName(s) in the group in the corresponding cell in column B ...

I have previously used a Pivot Table with a custom "GroupMembers" Measure ie (GroupMembers)=CONCATENATEX(AllGroupMembers, [MemberName], ",") - Rows = GroupName, Values = GroupMembers - which has worked fine until now ...

Unfortunately the source table has expanded to c.10,000 rows and I am now hitting the "This PivotTable field isn't available because it exceeds the total number of characters that a cell can contain." error ...

And no matter what I try I cannot find a way round it!!!

Can anyone help me please, either with a workround, or an alternative solution (eg can this be done in Power Query)?

Thanks ...

 

[RoryA]

If you have exceeded the maximum number of characters for a cell (which is over 32k characters), I don't see that PQ will help. What do you plan to do with the information?

 

[adambc]

If you have exceeded the maximum number of characters for a cell (which is over 32k characters), I don't see that PQ will help. What do you plan to do with the information?

@RoryA

The limit is 255 inside a Pivot Table (as I understand it?) ...

I need a list that shows the GroupName in eg A2 and all the Member Name(s) separated by a comma in eg B2 ...

 

[RoryA]

I just wrote a concatenatex measure that returned over 10k characters without error. Hopefully you turned grand totals off?

 

[adambc]

I just wrote a concatenatex measure that returned over 10k characters without error. Hopefully you turned grand totals off?

@RoryA

Grand Totals OFF

Can you post your example please?

Thanks ...

 

[RoryA]

It was the same measure as yours but for Table1 and [Name]

 

[adambc]

It was the same measure as yours but for Table1 and [Name]

In which case I don't understand - back to the drawing board!!!

 

[adambc]

I just wrote a concatenatex measure that returned over 10k characters without error. Hopefully you turned grand totals off?

@RoryA

Still no luck!!!

Some more detail ...

- the table (AllGroupMembers) has 9785 rows
- the GroupName column and MemberName columns are a 1:1 combination ie if a Member is a member of multiple Groups they appear on multiple rows (Note; there are no other columns)
- the GroupName column has 402 unique Group Names
- the MemberName column has 3203 unique Member Names

I can add the following Measure - =CONCATENATEX(AllGroupMembers, [MemberName], ",") - but when I try to add it to the Values Pivot Table Field, I get the error ("This PivotTable field isn't available because it exceeds the total number of characters that a cell can contain.") ...

I can only assume that there are some Groups, which have so many Members, that the 255 character limit is exceeded hence the error?

GrandTotals are OFF for Rows and Columns ...

Any more ideas?

Many thanks ...

PS; I can't share mile Workbook because it contains Personal Data ...

 

[RoryA]

The limit for a cell is not 255 characters, it's 32767.

Let me do some testing with a larger mock data set

 

[adambc]

The limit for a cell is not 255 characters, it's 32767.

Let me do some testing with a larger mock data set

Hhhmmm, I’ve been reading 255 in a Pivot Table …

But 32767 has got me thinking!

I think (I’ll check later) that there might be a Group to which ALL Members belong?

If there is, that’ll be the problem!!!