Tableau Prep: Week 002

This week 002 post is about how to use Tableau Prep to manipulate the below table, which shows the first 5 rows,

City	Metric	Measure	Value	Date
London	Max Temperature	Celsius	13	16/02/2019
London	Min Temperature	Celsius	6	16/02/2019
Londen	Precipitation	mm	2	16/02/2019
London	Wind Speed	mph	7	16/02/2019
London	Max Temperature	Celsius	14	17/02/2019

to the below format, which also shows the first 5 rows.

City	Date	Max Temperature_Celsius	Wind Speed_mph	Precipitation_mm	Min Temperature_Celsius
Edinburgh	22/02/2019	13	12	8	9
Edinburgh	20/02/2019	11	13	18	8
Edinburgh	16/02/2019	11	13	4	8
London	18/02/2019	11	10	12	4
Edinburgh	18/02/2019	9	20	11	5

The idea of this exercise is to

• To group the City same name into one with different spellings
• Combine the Metric & Measure fields into one new field
• Pivot the new field from column to rows to columns
• Sum the total for each new columns by City and Date

As shown on the below image, here are the steps to create the above output file.

Below are the steps to create the above table.

1. Read the input file by drag and drop or click File > Open
2. Clean the file
   • Create a new calculate file by combining the below two fields
     • Metric
     • Measure
   • Remove above both fields once the new field is creates
   • Change the format of the Value field to Whole Number
   • Filter out the City by excluding it
   • Group and Replace the different spelling variations of London into just London
   •  Group and Replace the different spelling variations of Edinburgh into just Edingburgh
3.  Pivot the new field from column to rows to columns
   • Drag the Metric_Measure field into Pivot Fields
     • Drag the Value field into Field to aggregate for new columns
4. Add another step to group and replace the null to zero for the following fields
   • Max Temperature_Celsius
   • Wind Speed_mph
   • Precipitation_mm
   • Min Temperature_Celsius
5. Finally, add the output step to save it as csv.

Attached are the Week 002 Solution.tflx.

The input file is from the following site, https://preppindata.blogspot.com.

I encourage you to follow that site to learn about Tableau Prep.