How to Create Aggregate Rows and Combine Values in Tableau

In our latest Action Bits. video, I discuss the nuances of combining values in Tableau Prep to streamline data analysis.

This post aims to elucidate this same process, offering step-by-step instructions and few additional insights to enhance your understanding and application of these techniques.

Step 1: Understanding the Scenario

We often encounter datasets with multiple entries (orders) associated with various items (products). The goal here is to aggregate these entries, combining all related items into a single, cohesive field for each order. This not only simplifies the data but also facilitates more efficient analysis.

Step 2: Preparing the Data

We begin by selecting our data source; in this case, we’re using the orders table from the Sample Superstore dataset provided by Tableau. The initial step involves streamlining the dataset by removing any unnecessary columns, leaving only ‘Order ID’ and ‘Product ID.’ This focuses the dataset, preparing it for effective aggregation.

Step 3: Employing Row Partitioning

Row partitioning is a pivotal technique in this process. Partition the data by ‘Order ID’ and order it by ‘Product ID.’ This assigns a unique, sequential row number to each product within an order, effectively organizing the data for aggregation. It’s crucial to note the number of rows per order, as this influences subsequent steps.

{ PARTITION [Order ID]: {orderby [Product ID]: ROW_NUMBER()}}

Step 4: Pivoting Rows to Columns

The pivot is where the magic happens. Transition your data from rows to columns, using the maximum value of ‘Product ID’ to ensure unique representation. This step is vital for consolidating the data, ensuring a clear and non-repetitive aggregation of product IDs for each order.

Step 5: Crafting the Aggregated Field

String together the product IDs using a calculated field. Incorporate conditional statements to manage null values and append commas as separators. The number of iterations in this step should match the maximum number of products per order noted earlier

[1]+
IIF(ISNULL([2]),'',', '+[2])+
IIF(ISNULL([3]),'',', '+[3])+
IIF(ISNULL([4]),'',', '+[4])
//IIF(ISNULL([N]),'',', '+[N])

Step 6: Cleanup

The cleanup is the final touch that ensures your data is presentation-ready. Remove all intermediary columns used in the aggregation process, retaining only the ‘Order ID’ and the newly created aggregated text field. This leaves you with a clean, concise dataset ready for analysis.

Aggregating rows in Tableau Prep can significantly enhance your data analysis efficiency, providing a clearer, more consolidated view of your data. By following these steps, you can transform complex datasets into simplified, manageable formats.

Here is the Tableau Prep workflow with all of the necessary steps to recreate the actions demonstrated in the video.

Check out our Action Bits page for more great Tableau tricks.