Shaping data for Tableau

In the previous section, we looked at the data format that works best in Tableau. We will now look at one of the two main examples where data is shaped ineffectively for Tableau and how we can easily change it to the correct format before beginning our data analysis.

Time-based data, especially financial data, is often stored in Excel with the dates in the column headers, the fields spread across columns in the first row, and values falling in the cells in the intersection. We can see this in Figure 1.6. This is a planning sheet for sales targets for the year 2022 for category sales of our SuperStore data:

Figure 1.6 – SuperStore sales targets format

If we connect this data to Tableau, it gives us the following metadata:

Figure 1.7 – SuperStore target default metadata

Thinking back to having fields in columns, a field type, and values in rows, we can see that this interpretation of the data is not helpful at all. What we want are three fields of the following types:

• Date: Type – date. There are 12 values, one for each month in 2022.
• Category: Type – string. There are three values: Furniture, Office Supplies, and Technology.
• Revenue target: Type – whole number

In order to get these fields into the right format, we need to pivot those date columns into rows and then rename two fields. Tableau makes this easy for us. Here are the steps:

1. Open Tableau Desktop.
2. Click on the Connect to Data blue hyperlink near the top left-hand side of the Tableau Desktop UI.
3. Choose Microsoft Excel. Navigate to the SuperStore 2022 Budget.xlsx file in the location you saved it on your computer. Click Open.
4. You will be presented with a screen similar to Figure 1.6.
5. In the Table Details pane, click on the header of Jan-22, hold down the Shift key, and click on the Dec-22 header to select all the date columns. You might find this easier if you first collapse the metadata pane:

Figure 1.8 – Multiselecting columns in the Table Details pane

6. Hold your cursor over the top-right-hand corner of the Dec-22 header to bring up the down arrow.
7. Click on the down arrow to bring up the menu of options. Choose Pivot.

Figure 1.9 – Menu options from the Table Details pane

8. You will now be presented with three fields. We are almost there!

Figure 1.10 – Table Details after the pivot

9. Our last step is to rename our three fields. We can do this by clicking on the field name and typing over the ones that are there, or by clicking on the same down arrow that we used in step 8 and using the Rename option.
10. Let’s rename our fields as follows: F1 to Category, Pivot Field Names to Date, and Pivot Field Values to Sales Target:

Figure 1.11 – Table details after renaming

11. Our last step is making sure our field types are correct. Category is a string field (alphanumeric) so it should show Abc, which it does. Similarly, Sales Target is a number and Tableau has it correct. Date is showing as a string. We would really like this as a date field because Tableau has special date-handling capabilities to make analysis much easier for us. To change the Date field to a type of date, click on Abc over Date and change the field type to Date:

Figure 1.12 – Table details – changing the data type

Don’t close this workbook; we will pick up from this point in the exercise in the next section, where we will combine data to our data model from other tables.

In this section, we looked at how we may need to reshape our data to make better data models in Tableau. In the next section, we will look at a use case where we need to add additional columns (fields) of data from other data sources to our model.