PivotTable with Multiple Text Values Alternative

If you have tried to insert a text field into the values layout area of a PivotTable, you may have noticed that the text string itself is not displayed. Instead, the count is displayed by default. In this series, we are talking about how to display the desired text values by using Power Query instead of a PivotTable. In the first post, we covered the steps needed when there is only one text value. We will now walk through the steps to join together multiple text values and separate them with a delimiter such as a comma.

Objective – PivotTable with Text Values

Before we get to the details, let’s confirm our goal here. We’ve exported some data from our system. In this illustration, it is the staff assigned to prepare client tax returns:

PivotTable Text Values Data Source

We would like to get it into a format like this, and note that there can be multiple staff assigned:

Desired PivotTable showing multiple text values in the layout area

In our previous post, we attempted to use a PivotTable. We quickly noticed that traditional PivotTables don’t really support the use of text in the values layout area, even when using the Min/Max options. So, we used Power Query as an alternative, and noticed that Min/Max do work with text strings. We could also use the Don’t Aggregate option. In the prior post, we only had a single staff assigned to each return, so Min/Max/Don’t Aggregate all returned the same result.

In this post, we will discuss how to handle the case of multiple text values, how Min/Max/Don’t Aggregate provide different results, and how to join the text values with the delimiter of your choice. Let’s do this thing.

Walkthrough – Power Query Alternative to PivotTable with Text Values

Let’s walk through the following steps:

  • Get Data into Power Query
  • Perform Transformations
  • Return Data to Excel

First things first … we need to get the data into Power Query.

Get Data into Power Query

In this illustration, our data is in an Excel table, but in practice, it could be just about anywhere. We begin by selecting Data > From Sheet. (Or, Data > From Table/Range depending on your version of Excel.)

The data is loaded into the Power Query editor:

Power Query data source

With that step complete, we can now perform our transformations.

Perform Transformations

In the previous post, we immediately performed a Pivot Column transformation at this point. That was because we did not have multiple rows for any given client return … in other words, there was only one staff assigned to each client return. However, in this data set, we have client returns with multiple staff assigned. If we were to use the steps shown in the previous post, we would see the following results based on our choice in the Pivot Column dialog (Advanced options):

  • Minimum – would return only one of the assigned staff (the first one, alphabetically)
  • Maximum – would return one staff (the last one, alphabetically)
  • Don’t Aggregate – would return an error (Expression.Error: There were too many elements in the enumeration to complete the operation)

So, our first transformation will NOT be Pivot Column. Instead, our first step is to Group By in order to shrink our table down to one row for each unique combination of Client/Return.

Group By

We start by selecting both the Client and Return columns (as shown above).

Then we select Transform > Group By. In the resulting Group By dialog, we define a New column name such as StaffTable and set the Operation to All Rows as shown below:

Group by command, similar to adding Client and Return into the PivotTable Rows layout area

We click OK and see a new column called StaffTable and it looks like it stores a bunch of Tables:

Condensed data source with one row for each unique client return

Think of each Table as a little mini-table that contains one row for each staff assigned to that specific Client Return. We can view each mini-table by clicking the empty space in any cell:

Display of table and resulting text values

We can now move to our next step.

Staff List

The StaffTable column contains a bunch of mini-tables. Instinctively, we understand that in general, a table can include multiple rows and multiple columns. We can verify this by viewing the mini-table in the screenshot above. We confirm there are two rows (one for DAR and one for DMK) and three columns (Client, Return, and Staff).

To accomplish our next step, we need to isolate the Staff column. While a Table can store multiple columns, a List can store a single column. So, our objective is to create a List of staff names for each client return.

One way to accomplish this is to create a new custom column by clicking Add Column > Custom Column. In the resulting dialog, we give our new column a name, such as StaffList. Then we use the Table.Column function to pull the “Staff” column out of the [StaffTable] table. The formula is shown below:

Custom function to create a list of text values

We hit OK, and we see a new column called StaffList that contains a bunch of Lists:

Resulting list of text values for the staff column

Think of each List as a little mini-list that contains a single column of staff assigned to each specific Client Return. We can see a preview of each list by clicking the empty space on any list cell:

Preview of the staff text values

We can now move to our next step.

Extract List – Separate Text Values

We want to convert each List into a single text value that we can send back to Excel. We have many options for combining the text values, including separating them with commas, colons, or spaces. In our case, we’ll use a comma-space to separate each.

We select the entire StaffList column, and then click Transform > Extract Values. In the resulting dialog, we can pick any delimiter from the list, or define our own by selecting Custom. I’ve entered a comma followed by a space:

Comma space delimiter to separate text values when combining them

We hit OK, and bam:

Results table

Now, we remove the StaffTable column by selecting it and clicking Transform > Remove Columns. And with that, we can head to our next step.

Pivot Column

We select the entire Return column, and select Transform > Pivot Column. In the resulting Pivot Column dialog, we select StaffList as the Values Column. We then expand the Advanced options and select Don’t Aggregate (or Minimum or Maximum):

Pivot Column command, similar to placing Client in the PivotTable Rows layout area, Return in the PivotTable Columns layout area, and StaffList in the Values layout area

We hit OK, and bam:

Power Query preview of the report (the alternative to a PivotTable)

Finally, we can send the results to Excel.

Return Data to Excel

We click Home > Close and Load To… and send the results back to a Table in the desired Excel worksheet:

The power query results table, showing the PivotTable alternative to displaying multiple text values in a report

Yay … we did it!

And the best part is that if the source data has any changes or new rows going forward, we don’t need to go through all of those steps again. We just need to right-click the results table and select Refresh!

Conclusion

I hope this alternative to a PivotTable report is useful when trying to display text values. If your workbook requires the use of a PivotTable, you can join text values together by using the data model and the CONCATENATEX DAX function. More info about that approach can be found here.

If this post was helpful, or if you have any suggestions for improvement or other alternatives, please share by posting a comment below!

Sample file

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

Leave a Comment