0:00Hey, it's Tim here, and welcome back to
0:03this third video in this Tableau prep for
0:06Excel
0:07users series. In this video, I'm going to
0:10be focusing in on one of the first steps,
0:12which is the cleansing data step. To do
0:15that, I'm actually going to open the sample
0:17flow
0:18found here on the bottom left of Tableau
0:20prep. Everyone has this in their default
0:22install
0:22of Tableau prep. If you don't, something's
0:25wrong with your installation. So click on
0:27this flow, open it up, and this will be
0:31what we're working with today. We're not
0:34going
0:34to actually build anything, we're just
0:36going to go through this particular flow,
0:38and we're
0:38going to add some additional steps to it
0:41and do some sort of data prep here. Now
0:43before
0:44I move on, I just want to go back to Excel.
0:47Now, if you're working in Excel and you're
0:50doing any sort of data cleansing, what you
0:52will typically be doing is working with the
0:54data itself. So this is literally the data
0:56that I'm highlighting here. You have lots
0:59of different data types. You can just see
1:01that there's text and there's numbers,
1:04there's
1:04some ID fields, but very much it's up to
1:06the individual to understand what's going
1:08on here.
1:09And this is the only visual sort of element
1:11and summary that you get. There's no sort
1:13of aggregation happening in the background
1:15to give you an overview of what's happening
1:18.
1:18Second more, let's say that I want to
1:20change some of the columns. Let's say that
1:22I'd like
1:22to maybe change some of the column headers.
1:25If I just scroll to the left here and go up
1:27again, I can just go in here, rename order
1:31dates to order date new, for example. And
1:34there you go, that's the sort of column
1:37name changed. But of course I can make a
1:40typo there
1:40and it's very easy to sort of make a
1:42mistake, change a wrong column maybe
1:44because that selection
1:45is basically down to human error. There's a
1:47few bits in other bobs. So a lot of sort
1:49of advanced users of Excel get very good at
1:51not making mistakes, which is fine, but we
1:54're
1:54all human. And even as a consultant, I've
1:56seen Excel files handed to me which have
1:58been
1:58told to me as gospel and using advanced
2:01tools like Alteryx or even Tableau Prep, I
2:04've been
2:04able to find mistakes in the data because
2:07the data was manually prepared. Now, the
2:10difficult
2:11thing here is you don't get an overview of
2:13what's going on. Let's say I just want to
2:16check which columns here have text as data
2:19types. Well, of course you'd say that
2:22columns
2:22like state, city, country are all basically
2:25just text, but you might also think the
2:28date
2:29is text, but it's not. It's actually a date
2:31type here and it's very different in a
2:33database
2:34when you store dates versus storing text.
2:37Now, you and I would write this in exactly
2:40the same way, but a database actually
2:42physically perceive this in a different way
2:44. If I just
2:45choose general here and go back to the
2:46default, you'll see that it actually
2:48interprets this
2:49as a number. Okay? I think this is the
2:51number of days since the epoch timestamp
2:54essentially.
2:55And so if I just undo that step and put
2:57that back to a date, the other thing you'll
3:00notice
3:00is that I made that change for that one
3:03cell. Okay? Now, typically what you might
3:06do is
3:06you might want to apply this to the whole
3:08cell. So you'll do this, you'll select the
3:10column, you go back, you go back to format
3:12cells, and then you'll say general, you
3:14click
3:15okay, and there you go, you've made your
3:17data change. But you don't get to sort of
3:19see that
3:19visual thing. You have to just trust that
3:22all those changes have been made.
3:23Now, the other thing is the data roles in
3:26this dataset aren't apparent. For example,
3:30the country, city, and state, and postal
3:32code are geographical bits of information.
3:35In fact,
3:35a lot of good visualization tools can take
3:37this and help you do things like mapping or
3:39help you do things like figuring out
3:41regions and sort of demographic information
3:44that can
3:44be overlaid onto this. And so if you want
3:47to use this in a more interesting way, it's
3:49not good enough just to store this
3:51information as text. If I just go into this
3:54column, you'll
3:54see here that it's just got a general data
3:57type and it's just being stored simply
3:59there
4:00actually as a number, not even as text. So
4:02that's the actual text donation. So it's
4:05very
4:06difficult to work with this and actually
4:07trust what's going on in the background.
4:08Now, if I head over to Tableau Prep and
4:11head over to a step tool. Now, if you
4:13recall the
4:14previous video, the step tools are these
4:17flat lines here that you see. They kind of
4:19don't
4:20have any sort of shape to them. They're
4:21really difficult to describe actually. But
4:24the step
4:24tool I'm going to be focusing on is this
4:26particular clean step here. I'm just
4:28selecting it here
4:29in blue. When I select it, it will open up
4:32this summary window. Okay? And if you
4:34recall
4:34in the previous video, the summary window
4:37has context based on the type of tool you
4:39're
4:40using. So the join tool just before it has
4:42a very different summary window to the step
4:45tool. Okay? So it's important to get
4:47familiar with the kind of things you can do
4:49inside
4:50of the step tool. Now, the first thing to
4:52bear in mind is that you've got this sort
4:54of row across the top. This is essentially
4:56your toolbar. And you should be aware that
4:59this changes depending on the column that
5:01you're working on and the data type. So let
5:03's
5:03say if I select the approve a column, I get
5:06a whole bunch of sort of tools available to
5:09me like splitting, filtering, creating a
5:12calculation. If I go to this numerical one,
5:14it changes
5:15because I'm now working with an integer
5:17rather than text. So I lose the ability to
5:19split
5:20it like as I would do with text, but I keep
5:22some of the other capabilities. If I go to
5:25a date column, let's look at order date. It
5:31doesn't change. But the difference here is
5:33that Tableau is actually applying this as a
5:36date. So if you click on this icon here,
5:38you'll see that there are lots of different
5:39data types. So Tableau is also visually
5:40telling
5:41me the data type for each column. And for
5:43some of you, you might say, well, the
5:45summary
5:46view is a little bit sort of unfriendly. I
5:48'd still like to be able to see the data.
5:51Well,
5:51that's exactly what you're getting here
5:53below. This preview at the bottom is
5:54actually a Tableau
5:55view of how that data looks like in Excel.
5:58And the really powerful thing here is if I
6:01just select maybe the items that take about
6:04one day to ship, let's really narrow this
6:07down. I'll hold Command, and in this
6:10particular example, I'll see items with a
6:13zero with a
6:1410% discount. I've narrowed this down to
6:17just one row, and I actually get to see
6:19that particular
6:20transaction there. It's very easy just to
6:23see that and scroll across, analyze it, see
6:25what's going on. This is particularly
6:27useful for finding problems because this
6:29aggregate
6:30view here at the top gives me that high-
6:32level view. Imagine like an eagle flying
6:35over a
6:35landscape. From a very high level, they can
6:38figure out what's going on, where to go,
6:41which
6:41way to head, where to find resources. And
6:43then in this Tableau view, you can actually
6:46drill into the problem and maybe work out a
6:48data cleansing solution and go from there.
6:50Now, if I zoom into this icon here, this is
6:53the integer icon, you'll also see that you
6:56get another sort of type of data type, and
6:59Tableau calls them data roles. You have
7:02emails,
7:03URLs, and geographic data roles. If I just
7:06sort of scroll down here a little bit,
7:08sorry,
7:09zoom out, then I have to zoom out here a
7:12bit, then I can zoom back in. If I just
7:15sort of
7:16go up and down this list, you can see that
7:18Tableau understands lots of geographic sort
7:20of data types. So it's actually brought
7:22that here into Tableau Prep, and I can
7:24actually
7:24specifically assign those data roles to a
7:27particular column. This then means that
7:29when
7:29I push this data onto Tableau or other
7:31tools, they'll automatically pick that
7:33metadata up
7:34and start using that to help with
7:36visualization or whatever those tools do.
7:39Maybe it's an
7:39analytical solution, whatever. If they can
7:42use these kinds of metadata, then you can
7:44put them here inside of Tableau Prep. In
7:47Excel, I have to do that manual sort of job
7:48of going
7:49in and figuring out what's going on. Now,
7:52the advantage here is that Tableau doesn't
7:54just leave you hanging. If you remember
7:56here at the top, we've got our tool pane.
7:58There's
7:58this section which looks at recommendations
8:00. You know, these are things that Tableau
8:02Prep
8:03thinks you can do with your data, okay? And
8:05you can see here that it is actually
8:07suggesting
8:08certain data roles for certain columns. And
8:11if you look at these, you can see that it's
8:13suggesting the city data role for the
8:15column name city. There's no surprise as
8:17far as they're
8:18literally called the same thing. So I'm not
8:20sure that's even machine learning or AI. It
8:22's
8:22just being smart. Postal code, country,
8:25state, these are all geographical items.
8:27And there's
8:27actually a geographical sort of equivalent.
8:30And so Tableau is just suggesting that we
8:33do that. If I zoom back out and I click on
8:37the city, you'll see that it actually zooms
8:40in on the city column here in blue. And it
8:42shows that recommendation at the top. And
8:44if we zoom into that, it just says here, "
8:46Many values match this data role." I.e., it
8:49's looked
8:49inside of this column and has realized that
8:52many of the values actually match cities
8:54that
8:55Tableau is aware about. And so it's
8:57suggesting that change. And if you hit
8:59apply, nothing
9:00happens to that column. It stays exactly
9:02where it is. But you can see here that it
9:04now has
9:04this new data role which is city. And you
9:07can see that this icon has been applied.
9:10Now
9:10this icon is a change. And different
9:12changes have different icons. If I zoom
9:15back out,
9:17you'll see that this particular step
9:21already has four changes. If I click this
9:23triangle
9:24to expand it, you'll see that there have
9:26already been changes made. There were three
9:28changes
9:28actually prior to me making that change for
9:31the data role. Now the way I know that is
9:34if I look at the clean step up here and I
9:35zoom into that, you'll see that I actually
9:37have icons at the top. And if I zoom back
9:40out and then just move this up, you can see
9:43that it actually tells me all the steps. So
9:46I have two remove fields, removing order ID
9:49and product ID. Then I have one rename step
9:52. And then I change my role to city. And for
9:55every step that we add, those will appear
9:58here on the left hand side. And the number
10:00of changes will either change to four, or
10:03if you've got this open, you'll see them
10:04sort
10:05of accumulating here, almost like a shelf.
10:07Okay. Now the powerful thing here is you
10:10don't
10:10have to go with that. Let's say you want to
10:12remove a step. Well, these aren't
10:14sequential
10:15changes as such. You can sort of remove
10:17them and ad hoc. So let's say I didn't want
10:19to
10:19remove my product ID field. I can simply
10:21delete that step and Tableau will run the
10:23remaining
10:24steps in sequential order. Okay. If I go
10:27back one step and add that back in, any new
10:30step
10:30that I add is going to appear below here.
10:33Let's say maybe I want to do that
10:34differently.
10:35Let's say I want to change the order in
10:37which these steps run. I can simply drag
10:39that particular
10:39step further up and it changes the way that
10:42everything operates. Now that in some
10:44instances
10:45might meaningfully change the way the data
10:47works. So if that's a useful step, then it
10:49's
10:49a good thing to be aware of. And then you
10:51'll see what I did here is I clicked on the
10:53final
10:53step and Tableau has continued that data
10:56processing all the way through. Now, one of
11:00the last
11:00things to think about is you might want to
11:02remove certain rows. Maybe you want to
11:04remove
11:05certain null values. Maybe, for example, I
11:07'm only analyzing return cells. And so I'm
11:09only
11:09interested in looking at the cells which
11:12have return reasons. I can simply right
11:15click on
11:15null and select exclude. And those are
11:18removed from my data set. And you get the
11:21sort of
11:21visuals here updating these little bar
11:23charts that you get in each column visually
11:26update
11:26to show you how much more of the data is
11:28left in proportion of the sort of entire
11:30context.
11:31And don't forget that change that we made
11:33that's filtered here on the top right of
11:35this
11:35column is also here on our pane on the very
11:38left hand side. Now, the last thing I want
11:40to show you is some of the sort of unique
11:42aspects of Tableau Prep and the way it can
11:44help clean data. Okay? I want to find a
11:47column called approver. And the approver
11:50column is
11:50very interesting because if I zoom into it
11:52here, you'll see that at the very top, you
11:54've
11:54got two sets of names, E. Williams and C.
11:57Arnold. And they're spelled in every sort
12:00of different way. And one of the things you
12:02might do is group these together in some
12:04way
12:04or form. In Excel, this might be a manual
12:06job just going in and just, you know,
12:08selecting
12:09the records and pasting the full name just
12:11to get a nice clean data set. You might set
12:14an intern on this and get them to clean a
12:16whole bunch of data. Well, that's not the
12:18smart way to do it. Tableau Prep has a
12:20really cool feature here that allows you to
12:23group
12:23items that sound the same. And so as well
12:26as filtering, which we did before, you can
12:28also group and replace items based on
12:31different factors. So in this particular
12:33case, I could
12:34do it manually. But some of the more
12:36powerful thing is using pronunciation or
12:39characters
12:39that are common across the set or maybe
12:41even spelling. So let me just use
12:43pronunciation
12:44and see what happens. It opens up this new
12:47interface. And I get this slider that tells
12:50me on the strength of a particular grouping
12:52. And anywhere where Tableau has actually
12:55achieved
12:55a grouping, you'll see here that it
12:57actually has this paperclip icon to the
12:59very left.
13:00And it shows you how many rows have been
13:02thrown into that group. Okay? If I click on
13:05"See
13:05Arnold," you'll get to see all the
13:07subsequent items that fall within that
13:09grouping. And
13:10if you don't like something, you can just
13:12simply untick it to remove it and it will
13:14appear again. But if you want to, you can
13:17click it from the outside and group the
13:20values
13:20and that will bring it into sort of the
13:22grouping. And you'll see that it pops back
13:24in there.
13:25Okay? So the better way to do it is to
13:27absolutely use the pronunciation of the
13:29smart algorithms.
13:30But you can absolutely do a manual grouping
13:32as well. I'll just type in "See Arnold"
13:35here
13:35because I've done a manual group. Hit enter
13:38. And now that grouping has been carried out
13:40.
13:41You also get the bar charts updating to
13:43reflect that. If I click on "Al Jenkins,"
13:46you'll see
13:46here that there's not many sort of other
13:49items here. But "Al Jenkins," "Al Smith,"
13:52and some
13:53of the other groupings seem to not be
13:55needed. It's just those two names that were
13:57causing
13:58an issue. So when you're done, you can just
14:00hit "Done" and we are done. I can zoom back
14:02out. And now that step has appeared over
14:06here. The group in replace uses this paper
14:09clip icon
14:09that you see here on the left. Okay. The
14:14last thing to do is to show you some of the
14:16really
14:16cool functionality that TaboPrep has. I've
14:19selected the "Approver" column. And you can
14:20see, as I showed you in the previous video,
14:23that these lines reflect where this "Appro
14:25ver"
14:25column is coming from. It's coming from the
14:28returns data and where it's going to. Okay.
14:31So the "Approver" column won't be found in
14:33any of my other datasets. So you can sort
14:35of trace the history of this column, as it
14:38were, through your data operations. The
14:40other
14:41thing to bear in mind is that as you work
14:42through different data types, let's say I
14:44click on this column. This keeps changing.
14:47But more importantly, I can create
14:49calculations
14:50and start working with really interesting
14:52sort of logic. Okay. And the way to do that
14:55is simply to select the column. And I can
14:57just select the option here to create a
15:00calculated
15:00field. With most things Tableau, there's
15:02always more than one way to do it. So this
15:04is one
15:05option. I could also click on these three
15:07dots, create a filter, and then have a
15:09calculation
15:09filter based. I can also just go ahead and
15:12create a new calculation here on the right
15:14hand side, just by going ahead and
15:16selecting "Create Calculation." Okay. So I
15:19'll just
15:20create a calculated field, and it opens up
15:21this interface. Now in the next video, I'll
15:24dive deeper into formulas and calculations.
15:27And so I'm not going to do anything here.
15:29But just to be familiar, you get all the
15:31different groupings like you would do in
15:32Excel. So if
15:33I scroll down, you'll see all the functions
15:36that you're used to. Date diff, date trunc
15:40ation,
15:40date, lengths, you know, all the kind of
15:43interesting things. Now, you won't see
15:46things like VLOOKUP,
15:47because VLOOKUP is actually a different
15:49type of operation that I'll go into later
15:51on, which
15:51is more synonymous with database joins. And
15:54so that's how Tableau will group that item
15:56and sort of term that particular operation.
15:59Right. That's pretty much the step function
16:02.
16:02Every time you add a step, you get the full
16:04power of everything I've just described in
16:08the setup. So if you look at some of these
16:10other steps, it goes to this step here.
16:12Although
16:12we've only got four columns, as I click
16:14through these, you'll see those options
16:16appear.
16:17Again and again, if there's no
16:18recommendations, you won't see the
16:20recommendation window appearing
16:21here. But this is a really, really powerful
16:24way to have a look at your data and work
16:26with
16:26it. If you want to make this Tableau view
16:29fill this space, you can just click on the
16:30second icon, and you'll get a little bit
16:32more data. And if you want to just see the
16:35columns
16:35and their own without any data, you just
16:37click this final icon here on the right
16:39hand side.
16:40And you can actually see what's going on,
16:42along with any changes that have been made
16:44in this particular step. So if you're
16:46making changes to a column, this is an
16:49amazing place
16:49to see how that's being made and what
16:52exactly is going on. Now, one thing I'd
16:55like to do
16:56is just show you one other thing that's
16:58very, very easy to do here in Tableau Prep.
17:01Let's
17:01say I'm not happy with the order of these
17:03columns. I can just simply move them around
17:06.
17:06Job done. In Excel, that would be much
17:08harder. I mean, I'm not a great Excel user,
17:11but my
17:11understanding is you'd have to insert a row
17:13, move the column across, and then move the
17:16column into where you want it. So you have
17:18to create the space to make the space. It
17:20just gets a little bit complicated. In
17:21Tableau Prep, you can just click and drag,
17:23and there
17:24you go. Again, if you want to rename it,
17:26just click on the name of the column, order
17:28ID.
17:28I'll call this new. Hit enter, and there
17:31you go. We've just renamed the particular
17:33field.
17:33That's considered a step. Okay, so if you
17:36do that step last, it's not ideal. You
17:38might
17:39want to do it a little bit earlier on in
17:41the flow, but then again, that might change
17:43subsequent
17:44steps. You'll see here that it's actually
17:46broken this particular step because it's
17:48saying
17:48it can't find order ID one. So just be
17:51careful with the way that you do your
17:53operations.
17:54If you want to get rid of it, you
17:56absolutely can delete the step, and that
17:59error goes away.
18:00Okay, that's been the video. If you've
18:02enjoyed the content, if you enjoyed the
18:04video, let
18:05me know. These are a little bit longer than
18:07usual, and thank you for bearing with me as
18:08I sort of take you through the basics of
18:11Tableau Prep. If you would like a different
18:14kind of
18:14video about Tableau Prep or even Tableau,
18:16drop a comment below, let me know, give me
18:18some
18:18feedback. Otherwise, make sure you
18:20subscribe so you catch the latest content.
Tableau | Data prep | Data visualisation