Preppin' Data

Tableau Prep Builder (as of version 2020.3) doesn't have a simple way to add the data source name or table name into your data set if it's not in the actual data set itself. But I've found a neat trick of how to achieve this! The Challenge Useful data points come from many places. Sometimes those places are staring you straight in the face! Take this example where the Excel Sheet name contains a date that needs to be added to data set. The data set is taken from Preppin' Data's 2019 Week Challenge  if you want to try this yourself.   In this instance, we'd want to grab the 'Mar 2019' part of the table name to show the reporting date for the contents of the table. When inputing this data set into Prep, the table name isn't included by default.  The Solution A sneaky solution came to me when using actually planning another post. When Unioning files together, Prep automatically adds an additional data field called Table Names. This Table Name references...

When Tableau Prep originally was released, the tool was designed to prepare data for Tableau. By cleaning and manipulating data easily and effectively, Prep solved issues for more than just data to be used in Tableau. Therefore, the demand grew for the ability to create, not just Tableau Extracts or CSV files, but writing back to the original sources of much of the information - databases. This was a significant departure for Tableau as Desktop had always been a read only tool, so allowing Prep to change data permanently opened up opportunities, as well as risks. This post will cover when you should look to write back to the database, how to write back to database and what to watch out for when doing so. When should you write to a Database? As we've seen in all the other 'How to...' posts, messy and multiple datasets can take a lot of time to prepare. If you have taken the time and effort to make the data suitable for analysis, then it is likely you should make it ava...

This challenge is a collaborative effort from Jenny, Carl and Lorna . After the success of last year's Preppin' Data and Workout Wednesday live event, we decided a second meeting of the two challenges was a must. Although we can't be together physically in person, thank goodness we're able to host the event virtually! Unfortunately, the Olympics that were due to take place in Japan this year weren't so lucky. Therefore, we chose to focus our challenge on looking back at historical Olympics data. Did you know this isn't the first Olympics to be cancelled? Inputs There are 3 inputs this week:  Host Countries (which you may recognise as the output from week 28's challenge , whose input came from Sports Viz Sunday )  The history of all the medallists for each Olympics (sourced from the Guardian  and Wikipedia ) A country codes lookup table. Requirements Input the data Make sure every medal has both a Country and Country Code associa...

Solution post by Tom Prowse Download our full solution here . For this week's challenge we looked at a slightly different input method, by using Google Sheets as a data source so that you can fully refresh and make it relevant by inputting your own city! However, for the solution post, I'll use an example from an Excel document so that you can download and see the solution. If you wanted to connect to a Google Sheet then the process is explained within the challenge post .  Step 1 - Replace Characters For the initial steps, this will be repeated for all three tables (Next 5 Days, Next 24 Hours, and Next 5 Hours). However, as the tables have different structures, we will still split them out into three different work streams.  The first step this week is to split the string so that each category is within it's own column. The categories are broken up by the '\n' which normally represents a 'new line', however the Split functionality within Tableau Prep doesn...

This challenge is from the mind of Carl. I'm British. As a nation we are obsessed about the weather (almost as much as I'm obsessed about Prep). I was really hoping I was going to be able to create a fun incremental refreshing challenge about the weather but I failed to find a data source that has either a number or date in the initial load that Prep can reference for refreshing. Instead, I have come across a data source you can fully refresh and make relevant for yourself.  The data source I have used for this challenge is from weather.com. We'd love to see you use your own city's data for this week challenge so the values will differ but the structure should stay the same! Here's how I got the data source this week: 1. Go to weather.com  2. Search for your city (or use London if you want to see some Precipitation percentages!) 3. Take the URL of the page the search returns. it should look something like this (with a lot of advertising my screenshots are trying to ...

Solution post by Tom Prowse Download our full solution here . The challenge this week looked at refunds and what happens when events get cancelled. In the challenge we looked at creating email lists and also exchange rates.  Step 1 - Join Data The first step this week is to combine all three of our data tables, into one data source. To do this we can start with joining the Attendee List and the Account Manager tables. Before joining we need to identify which company each of our attendees is from, by extracting the company name from their email address.  To achieve this we can use a couple of splits: Custom Split - Everything after the '@' symbol. Custom Split - Everything before the first '.' As a result of this, we should have a list of the company names: In some other tools, we would need to prepare our able Company list a little further due to the different cases (eg, the first letter being a capitalised) but Tableau Prep recognises this and will join our fields toge...

Week 29 by Tom Prowse Unfortunately, Chin & Beard Suds Co have had to cancel their world famous conference event and this has left a lot of people unhappy! As a result, we need to process the refunding of tickets to each of the attendees who have already paid.  Each attendee paid the equivalent to £100 (GBP) for a ticket and they have a few options about how they can have their refund:  Full Refund - This is a full cash refund back to them. Credit Note - We can create credit notes to be used next year. No Refund - They can waive their refund and give us the money.  Each refund will be refunded in the currency of their selected country. We have been given various data sources and we need to combine them to answer the following questions. Who does each Account Manager (AM) have to contact about processing the refunds? How much money will we lose/make from each of the different refund scenarios? Input One Excel file with multiple tabs. Note, all of this data is crea...