Tidy up the data

The practice file is messy on purpose: unused fields (Notes, InternalCode), 7 rows without a Region, a lowercase "jakarta" and a "Bandung " with a trailing space. The "jakarta" value needs no fixing: Tableau ignores upper and lower case in Excel text, so it is merged with "Jakarta" automatically. In Tableau, every fix is saved in the workbook; your Excel file stays unchanged. Continue in the same workbook as the previous lesson (the Sales data from insyntive-sales-sample.xlsx has been connected since Module 1).

12345 Zoom

Click a number on the picture or in this list to highlight that area.

1. 1Hide Hides fields you don’t use, such as Notes and InternalCode.
2. 2Change Data Type Changes a field’s type, e.g. Date & Time to Date.
3. 3Null unticked A Region filter without Null drops the 7 rows with no city.
4. 4Group The Bandung group (paperclip icon) merges two Bandung members, one of them "Bandung " with a trailing space. The Excel file stays unchanged.
5. 5Rename Right-click a field → Rename to give it a new name.

Step by step

1. 1

   Step 1: In the Data pane, click Notes, hold Ctrl (Cmd on a Mac), then click InternalCode. Right-click either one → Hide (number 1). Both fields disappear from the Data pane and stay out of your way.

   Tip: Hidden fields are not deleted. To bring one back: click the small menu icon at the top right of the Data pane (next to the Search box) → Show Hidden Fields, then right-click the greyed-out field → Unhide.

2. 2

   Step 2: Check the type icon in front of each field: Abc = text, # = number, a calendar = date, a calendar with a clock = date and time. If Date shows a calendar with a clock, right-click Date → Change Data Type (number 2) → Date, because the practice data has no times.

   Watch out: If Revenue or Cost shows Abc, the numbers were read as text and can’t be added up. Fix it with Change Data Type → Number (whole).

3. 3

   Step 3: Drop the 7 rows without a Region for every sheet at once with a data source filter. Click the Data Source tab at the bottom left → at the top right, next to Filters, click Add → in the Edit Data Source Filters window click Add... → choose Region → OK. In the Region filter window, click All, untick Null (number 3), then click OK twice. This filter doesn’t show on the Filters shelf, but it applies to every sheet.

   Tip: Need it on one sheet only? The quick way: right-click the Null header in the view → Exclude.

   Watch out: In Tableau Desktop, near Filters you’ll also see a Connection: Live / Extract choice. Leave it as it is. Tableau Public always stores the data as an extract, so there you don’t need to choose.

4. 4

   Step 4: Go back to your sheet tab. Right-click Region → Create → Group.... You’ll see two Bandung entries that look identical (one has a trailing space). Click the first, hold Ctrl (Cmd on a Mac), click the second, then click Group (number 4). Click Rename, type Bandung, then click OK. There is only one Jakarta in this list, so it needs no grouping. Finally, use right-click → Rename (number 5) to change Region to Region (raw) first, then Region (group) to Region.

   Tip: With those names, whenever a later lesson says Region, you’re already using the tidy version. Group fields are marked with a paperclip icon.

   Watch out: A group only holds the values you picked today. If your data ever gets a new misspelling (e.g. "Medan " with a space), add it via right-click Region → Edit Group....