how to add multiple rows in excel pivot table

How to add multiple rows in excel pivot table how to#

We want to convert each List into a single text value that we can send back to Excel. We can see a preview of each list by clicking the empty space on any list cell: Think of each List as a little mini-list that contains a single column of staff assigned to each specific Client Return. We hit OK, and we see a new column called StaffList that contains a bunch of Lists: Then we use the Table.Column function to pull the “Staff” column out of the table. In the resulting dialog, we give our new column a name, such as StaffList. One way to accomplish this is to create a new custom column by clicking Add Column > Custom Column. So, our objective is to create a List of staff names for each client return. While a Table can store multiple columns, a List can store a single column. To accomplish our next step, we need to isolate the Staff column. We confirm there are two rows (one for DAR and one for DMK) and three columns (Client, Return, and Staff). We can verify this by viewing the mini-table in the screenshot above. Instinctively, we understand that in general, a table can include multiple rows and multiple columns. The StaffTable column contains a bunch of mini-tables. We can view each mini-table by clicking the empty space in any cell: Think of each Table as a little mini-table that contains one row for each staff assigned to that specific Client Return. We click OK and see a new column called StaffTable and it looks like it stores a bunch of Tables: 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: We start by selecting both the Client and Return columns (as shown above). 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. So, our first transformation will NOT be Pivot Column. Don’t Aggregate – would return an error (Expression.Error: There were too many elements in the enumeration to complete the operation).Maximum – would return one staff (the last one, alphabetically).Minimum – would return only one of the assigned staff (the first one, alphabetically).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): However, in this data set, we have client returns with multiple staff assigned. 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. In the previous post, we immediately performed a Pivot Column transformation at this point. With that step complete, we can now perform our transformations. The data is loaded into the Power Query editor: (Or, Data > From Table/Range depending on your version of Excel.) In this illustration, our data is in an Excel table, but in practice, it could be just about anywhere. Walkthrough – Power Query Alternative to PivotTable with Text Valuesįirst things first … we need to get the data into Power Query.

How to add multiple rows in excel pivot table how to#

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. 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. We could also use the Don’t Aggregate option. So, we used Power Query as an alternative, and noticed that Min/Max do work with text strings.

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. In our previous post, we attempted to use a PivotTable. We would like to get it into a format like this, and note that there can be multiple staff assigned: In this illustration, it is the staff assigned to prepare client tax returns: We’ve exported some data from our system. Objective – PivotTable with Text Valuesīefore we get to the details, let’s confirm our goal here. We will now walk through the steps to join together multiple text values and separate them with a delimiter such as a comma. In the first post, we covered the steps needed when there is only one text value. In this series, we are talking about how to display the desired text values by using Power Query instead of a PivotTable. Instead, the count is displayed by default.

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.