Pivoting data in Tableau Prep

Wrestling with data from a spreadsheet or crosstab can prove to be difficult in Tableau with its preference for tall data as opposed to wide data. Pivoting data from columns to rows can help Tableau evaluate the data effectively. Conversely, the you could have data that is too tall and narrow to be properly analysed, and in this case we would want to have the data as separate measures. This would require a pivot from rows to columns.

This blog will cover the 3 different options when looking to pivot your data. The explanations will be for carrying out pivots in Tableau Prep, but the principle remains the same on other software's. If you want a walkthrough example, watch the following video below (coming soon).

Pivoting columns to rows

Use: when requiring to go from wide to tall data.

1. After connecting to your data, drag your table into the Flow Pane.
2. Click the + icon and select the Pivot option from the menu.
3. Select the fields from the left pane and drag to the "Pivot1 Values" column in the Pivoted Fields pane. (Note: In the fields pane, use the search field to search the fields for those you want to pivot, if you have many fields in your data set).
4. Edit the names of the created fields.
5. Refresh your pivot data when the data changes, and run your flow. (Make sure to rename the pivot step in your flow to keep track of the change you made.

Wildcard search to pivot fields

Use: When working with large data sets or frequently changing data, the wildcard search can prove a helpful tool. You can pivot based on a wildcard pattern match.

1. After connecting to your data, drag your table into the Flow Pane.
2. Click the + icon and select Pivot from the menu.
3. In the Pivoted Fields pane, click on the use wildcard search to pivot option.
4. Enter a partial/full value you want to search for. E.g.

Then, click the Search options button to select how you want to match the value. Select Enter to apply the search and pivot the values matching your specifications. (If you want to add more columns, in the Pivoted Fields pane click the + icon to add more to pivot on)

6. Edit the names of the created fields.
7. Refresh your pivot data when the data changes, and run your flow. (Make sure to rename the pivot step in your flow to keep track of the change you made.

If the results differ from your expected output, try entering a different value pattern in the search field. Alternatively, drag addition fields to the Pivot1 Values columns.

Pivot rows to columns

Use: if data is too normalised and you need to create new columns.

1. After connecting to your data, drag your table into the Flow Pane.
2. Click the + icon and select Pivot from the menu.
3. In the Fields pane, you can use the Search Field to search for fields to pivot. Select a field, and drag it to the Field that will pivot rows to columns section in the Pivoted Fields pane.
4. Select the fields from the left pane and drag to the Pivot1 Values column in the Pivoted Fields pane. (Note: In the fields pane, use the search field to search the fields for those you want to pivot, if you have many fields in your data set).
5. Select a field from the left hand side, and drag it to the "Field that will pivot rows to columns section in the Pivoted Fields pane. (Change the columns in the Results pane)
6. Select a fields from the left hand side, and drag to Field to aggregate for new columns section in the Pivoted Fields pane. (The aggregation type can be changed in the aggregation type menu)

Pivoting is a vital part of data preparation. By understanding how to transform data, it ensures that data visualisation is a smoother process with having the data orientated correctly.