Preppin' Data

Solution by Tom Prowse and you can download the workflow here .   This is the final week of our introductory challenges so hopefully you are starting to build up knowledge of different techniques that you can use within Tableau Prep.  We're looking at customer acquisitions this month and combining different tabs from a spreadsheet so we can consolidate and analyse the data from different tables.  Step 1 - Input Data After connecting to our New Customers data source, we then want to bring in the sheet for January (or any month). Within the input step, we can control if we include a single table or multiple table by selecting the radio button.  When selecting 'Union Multiple Tables' this allows us to create a union straight from the input step based on various conditions. In our case we want to include multiple tables from where the filename matches New Customers.xlsx, but in some cases you can limit the tables that you bring in by setting a matching pattern. Note, dep...

Challenge by: Jenny Martin It's the final week of beginner month already! Hopefully you've picked up a lot of new skills and have enjoyed the challenges. We'll add another new technique and build on your existing skills with this challenge. Data Source Bank acquires new customers every month. They are stored in separate tabs of an Excel workbook so it's "easy" to see which customers joined in which month. However, it's not so easy to do any comparisons between months. Therefore, we'd like to consolidate all the months into one dataset.  There's an extra twist as well. The customer demographics are stored as rows rather than columns, which doesn't make for very easy reading. So we'd also like to restructure the data. Inputs We have one excel file, with 12 different tabs, one for each month: Requirements Input the data We want to stack the tables on top of one another, since they have the same fields in each sheet. We can do this one of 2 way...

Solution by Tom Prowse and you can download the workflow here . We continued our introductory month based around the Data Source Bank. This week we looked at including targets and combining the data sources to extend our analysis. Step 1 - Transactions File First we want to prepare the Transactions file input so that it is ready to combine with the Targets table.  After inputting the data we need to filter the table so the transaction code only contains values with 'DSB' in them. For this we can use a 'Wildcard Match' filter:  Next we want to update the Online or In-Person field from a number to a string. Once it is a string, we can then rename the fields where 1= Online and 2=In-Person.  Then we want to extract only the Quarter from the Transaction Date field. To do this we can make use of the in-built functionality within Tableau Prep that includes converting dates:  Finally, we want to find the total Value by Quarter and Online or In-Person so we need to use an ag...

 Challenge by: Jenny Martin For the third week of beginner month, we're going to be building on the skills that we've already learnt, as well as exploring new concepts. This week may feel a little more challenging, but I promise you're ready for it! Data Source Bank has some quarterly targets for the value of transactions that are being performed in-person and online. It's our job to compare the transactions to these target figures. Inputs The same transactions file as the first week's  Quarterly Targets dataset  Requirements Input the data For the transactions file: Filter the transactions to just look at DSB ( help ) These will be transactions that contain DSB in the Transaction Code field Rename the values in the Online or In-person field, Online of the 1 values and In-Person for the 2 values Change the date to be the quarter ( help ) Sum the transaction values for each quarter and for each Type of Transaction (Online or In-Person) ( help ) For the targets file: ...

Solution by Tom Prowse and you can download the workflow here .  For the 2nd week of our beginner month throughout January, we looked at creating IBANs for our fictional Bank. Step 1 - Sort Code First we want to connect to our data source and then bring in the Transactions table. From this table we want to remove the '-' between the digits of the 6 digit sort code.  There are various ways that we can do this, but Tableau Prep makes it really easy by having a 'Remove Punctuation' feature within the clean menu.  All we need to do is click on the three dot menu, then clean, then Remove Punctuation:  As a result the '-' is removed and all we're left with is the 6 digits. Step 2 - Swift Code Lookup Up next we want to include another table, this time from the Swift Codes input.  From here we can combine our original workflow with the Swift Codes by joining them together. By using a join we are placing the tables side by side and using a field to match the appropri...

Challenge By: Jenny Martin For week 2 of our beginner month, Data Source Bank has a requirement to construct International Bank Account Numbers (IBANs), even for Transactions taking place in the UK. We have all the information in separate fields, we just need to put it altogether in the following order: Inputs A list of the transactions, with information about the receiving bank account  A lookup table for the SWIFT Bank Codes  Requirements Input the data In the Transactions table, there is a Sort Code field which contains dashes. We need to remove these so just have a 6 digit string ( hint ) Use the SWIFT Bank Code lookup table to bring in additional information about the SWIFT code and Check Digits of the receiving bank account ( hint ) Add a field for the Country Code ( hint ) Hint: all these transactions take place in the UK so the Country Code should be GB Create the IBAN as above ( hint ) Hint: watch out for trying to combine sting fields with numeric fields - check data...

Solution by Tom Prowse and you can download the workflow here . It's the first solution post of the year! If you are new to #Preppin' Data then this is where we recap the challenge from the previous week and walk you through how we went about solving it. Each week we will have the blog post and video solution to help you get through the challenge. These might differ slightly but hopefully that will show that there isn't always a 'right' answer but there are many ways of getting to the desired outcome. Let's get started with the solution! Step 1 - Split Transaction Code First up we want to connect to our data source. We are working with a CSV file (text input) that contains all of the transaction information from our customers, including a transaction code that we can split apart to get some useful information. To split the transaction code, we can use the in-built split functionality within Tableau Prep. After creating a clean step, after the input, we can selec...

 Created by: Carl Allchin Welcome to a New Year of Preppin' Data. These are weekly exercises to help you learn and develop data preparation skills. We publish the challenges on a Wednesday and share a solution the following Tuesday. You can take the challenges whenever you want and we love to see your solutions. With data preparation, there is never just one way to complete the tasks so sharing your solutions will help others learn too. Share on Twitter, LinkedIn, the Tableau Forums or wherever you want to too. Tag Jenny Martin, Tom Prowse or myself or just use the #PreppinData to share your solutions.  The challenges are designed for learning Tableau Prep but we have a broad community who complete the challenges in R, Python, SQL, DBT, EasyMorph and many other tools. We love seeing people learn new tools so feel free to use whatever tools you want to complete the challenges.  A New Year means we start afresh so January's challenges will be focused on beginners. We will u...