Before we look at data preparation using Excel and Tableau Prep, we need to ask a question. It’s always a healthy discipline to ask questions first and use technology to answer them second.

Take a look at the question, look away from the screen then take one minute to jot down your responses. Try and answer with three words maximum for each challenge. The idea is that you don’t have time to list them all, but you will hit on the top five within a few seconds.

So here’s the question. Get your notepad ready: What are your current pain points when preparing data in Excel?

I did the same process, and here were my answers:

• Errors
• Inconsistency
• Time
• Manual
• Not fun
• Excel crashes
• Sometimes too complex

Maybe we share the same pain points, or maybe ours are completely different. It doesn’t matter too much what I think. What matters is that you have identified areas that are significant to you. Consider them as you read the rest of the article.

Captain Planet and the Planeteers

Does anyone remember Captain Planet? It was a kids’ TV show in the early ‘90s.

via GIPHY

The Planeteers were tasked with saving Earth from environmental disasters. They were given rings that could summon the power of five elements: earth, fire, wind, water and heart. When they couldn’t resolve a situation with their powers alone, they summoned Captain Planet who combined and magnified their powers. After saving the planet, Captain Planet would say, “The power is yours!”.

This exemplifies what we’re doing here. We have two powerful tools—Excel and Tableau Prep—and when combined, our data-preparation powers are magnified, so we can resolve many more situations. The power is ours!

Excel + Tableau Prep

Think of a regular piece of data-prep work you do in Excel that starts with exporting data from a source application (e.g. CRM, accounting system) to a CSV. Jot down the tasks you carry out to complete the data prep. For example:

• Filtering
• Deleting rows/columns
• Formatting data/text
• Splitting strings
• Stacking multiple sheets together (union)

Here’s a typical Excel datasheet. It shows sample receivables and payables invoice data with the first row being the headers and all the other rows being the data we want to prepare:

Right, take a deep breath: we’re not going to do any data prep in Excel!

Let’s jump straight to Tableau Prep. Something useful to note here is when you’re in Tableau Prep, you’re not messing with the original database. This allows you to play around until you get things right, and you can be safe in the knowledge you’re not going to need the undo button in Excel when something goes horribly wrong and you want to retrace your steps.

This is where you get a glimpse of your new ‘power.’ We’re going to use Tableau Prep to do all the data prep tasks for you. As soon as we connect to data in Tableau Prep, we get something we don’t often get with Excel. We get the feel for the data.

Tableau Prep automatically provides distributions and summaries of each column. This gives us a quick and easy way of feeling out the data for outliers and oddities, as well as a way of comparing with our expectations.

Starting off with Tableau Prep

Here’s the section feeling out the data:

We get to build a replicable workflow in an easy and intuitive interface. The great thing about this, and something we don’t get out of the box with Excel, is that we can run the workflow again and again on new data.

Here are examples of how those data-prepping tasks we mentioned earlier are made easier with Tableau Prep.

Filtering Your Data

Clicking on a segment of the data gives me not only options to Keep Only or Exclude, but it also highlights where those datapoints exist in the other columns. In the example below, I clicked on Payables:

Deleting Rows or Columns

With every data export come columns we want to jettison. Tableau Prep makes that super easy: select the columns to remove and consider it done. Remember, this doesn’t add or delete anything to or from the Excel file.

Formatting Data or Text

Want to change the format of the data in a particular column, e.g. change a string to a measure or vice versa? No problem.

Editing Strings

Do you have cells that contain data needing to be split? For example, perhaps first name and second name are in the same cell, or letters and numbers are in the same cell and you want these separated:

Maybe you need to remove letters, trim spaces, remove numbers, etc.

Unioning Multiple Sheets

This is great for when you have multiple sheets in the same workbook that you need to combine, e.g. combining the sheets January, February, March, etc. It even colour codes for an instant visual check to ensure you have not missed or gained a column from one of the sheets:

Creating a Flow

What are all these tasks leading up to? Flow and Output. Build your flow and output to Excel or a Tableau data extract, and you have your data ready for analytics and data visualisation:

Next Steps with Tableau Prep

There’s no time like the present. Go and download a free trial of Tableau Prep, and line up one of your existing data-prep workflows. Put it through the Tableau Prep model and see how it can change your world.

Happy data prepping 😊