Preppin' Data

Solution by Tom Prowse and you can download the workflow here .  This week's challenge is focussed on cleaning a pivot table that has a lot of fields (145!!). Although this might be good to look at in a Spreadsheet, it's not the best way of structuring our data in Tableau so let's look at how we can 'un-pivot' our data. Step 1 - Rename Fields First up we want to dynamically rename our fields so that there is a common separator between the Bike Type, Date, and Measure.  Now this could be a long manual task as there are 145 fields so it would take a seriously long time for us to go through each one. Luckily, Tableau Prep has a feature that allows us to rename our fields all in one go! Using the 'Rename Fields' feature we can replace the '___' with a single '-': This allows us to create the replace the separator between the Type and Date.  We can then repeat this process by replacing '_' with ' ' (space): At this stage our fields...

 Challenge by: Jenny Martin Have you ever been given a datasource to work with which has already been lovingly "cleaned" and turned into a pivot table? Whilst that's very kind of the provider, it's not exactly ideal for Tableau Desktop! Let's work to un-pivot such a table from a stakeholder at AllChains! Input A 145 (!) field pivot table: Requirements Input the data Dynamically rename the fields so that there is a common separator between the Bike Type, Date and Measure Name Pivot the data Split out the Bike Type, Date and Measure Name Create a field for Sales and Profit Output the data Output 5 fields Bike Type Region Month Sales Profit 288 rows (289 including headers) You can download the  full output here .  After you finish the challenge make sure to fill in the  participation tracker , then share your solution on Twitter using  #PreppinData  and tagging  @Datajedininja ,  @JennyMartinDS14  &  @TomProwse1 You can also post your s...

Solution by Tom Prowse and you can download the workflow here .  The challenge this week is to show how data prep can really help you out in situations where you are working across different levels of aggregation and want to save yourself from making complicated calculations within Tableau Desktop. Step 1 - Location Spelling Errors & Timestamps First we need to input the Streaming table into our workflow and then we can starting cleaning the spelling mistakes within the Location field. Currently, the value 'Edinurgh' is spelt incorrectly, so we can double click on this value within the Profile Pane and update it to the correct spelling 'Edinburgh'. Next, we can focus on formatting the date in the correct Data type, by changing it from a String to a Date & Time field. This will successfully remove the T & Z from 't' field so we can also rename the field to 'Timestamp'.  At this stage our data should now look like this: Step 2 - Content Type No...

Challenge by: Jenny Martin This challenge was inspired by a recent use case from a client. It involves bringing together 2 datasets which have different levels of aggregation. They were initially trying to do this in Tableau Desktop but getting a bit muddled with the calculations going on "in the background." So let's use the power of Prep to map out the process and make everything much clearer.  Inputs Streaming sessions from users  Avg Pricing for each month  Requirements Input the data Check the location field for spelling errors Data roles may help you identify these Fix the date fields so they are recognised as date data types Aggregate the data to find the total duration of each streaming session (as identified by the timestamp) We need to update the content_type field: For London, Cardiff and Edinburgh, the content_type is defined as "Primary" For other locations, maintain the "Preserved" content_type and update all others to have a "Second...

Solution by Tom Prowse and you can download the workflow here . We're looking at restaurant orders this week and how we can change the structure of the input so that it's easier to understand for when the chefs come to cook up the food. Step 1 - Input Data  First up we need to input the data and get this in a nicer format so that we can combine the orders with the recipe lookup table.  In it's current form, the table of orders is a hard one for Tableau Prep to understand because we can't associate what has been selected (🗸) with who made that selection.  Therefore, we need to split the Orders table into 4 separate inputs (1 for each person) and in each of the inputs we only want 2 fields - the name and the selection. 4 input steps:  Example of the fields retained from Jenny's input:  Once we have input the 4 separate inputs, we can combine these using a union step and then merge the fields so that all of the names are in a single field and the selections are in...

Created by: Jenny Martin The Preppin' Data team are planning to go out to dinner. However, the system the restaurant has used to collect orders doesn't provide the chefs with the easiest way to understand what they should be making. To help things go swimmingly on the night, as well as for all other guests using this ordering system in the future, we'll create a workflow to only show the ordered dishes for each guest. Inputs Orders from Guests  Recipe ID lookup table  Requirements Input the data Reshape the Orders table so that we have 3 columns: Guest name Dish Selections (containing 🗸 or null) Extract the course name from the Dish field Group these so that Starter and Starters are treated the same, for example Fill down the course name for each Guest ( hint ) It may help to bring in the Recipe ID from the Lookup Table  Where the Dish contains the Course name, it may be helpful to replace the Recipe ID in the following way: Starters = 1 Mains = 2  Dessert = 3 ...

Solution by Tom Prowse and you can download the workflow here .  This week we looked at a property management challenge, where we wanted to calculate the amount of rental revenue that our company is making from it's portfolio. The challenge was created by Tom Smith ( @tSmithyyy ) so let's take a look at how to solve it.  Step 1 - Contract Length First we want to calculate the length of each contract in months. To do this we want to input the Rental Contracts data then use a DateDiff calculation to find the length of the contract in months:  Contract Length   DATEDIFF('month', [Contract Start], [Contract End]) Then we can calculate how many months are left until the contract expires: Months Until Expiry   DATEDIFF('month', MAKEDATE(2022,04,13), [Contract End]) Then finally we can find the month of the contract start and end month by using the following calculations: Contract Start Month DATE(DATETRUNC('month',[Contract Start])) Contract End Month DATE(DAT...