0:00Hey, it's Tim here, and welcome to this
0:04first video in a new series called Tableau
0:06Prep for Excel users.
0:08I've basically taken this concept from Al
0:09teryx, who have a similar blog post titled "
0:12Alteryx for Excel Users,"
0:14but instead I'm applying it to Tableau Prep
0:15. I think it's a worthwhile sort of theme to
0:18have,
0:19and it makes it very familiar for people
0:20who are coming from Excel, which tends to
0:22be quite a lot of people.
0:24What I'm going to do is break it down in a
0:26very sort of similar way.
0:28Alteryx have sort of these categories, so
0:30this video is actually about connecting to
0:32files.
0:33In subsequent videos, I'll cover these
0:35other topics you can see here on the screen
0:37.
0:38So here we are in Tableau Prep. I'm in the
0:41very sort of basic interface that you get
0:44when you open it for the first time.
0:45And so this is probably what you'll do.
0:47This is probably one of the first things
0:48you'll have to do.
0:49And there's a couple of ways of connecting
0:51to a file. I'll show you the first one.
0:53If I hit the "Connect to Data" option here
0:55at the top, I'll get a list of the
0:57available file formats.
0:59And this is a very sort of important list,
1:01because this list is constantly updating
1:03month to month.
1:04Tableau is adding new connectors and new
1:05file formats to this every single month.
1:08So be in tune with the version and try and
1:10make sure that your data type is supported
1:13here before getting involved with Tableau
1:15Prep.
1:16Now that said, today I'm connecting to
1:17Microsoft Excel. That's going to be this
1:19option here.
1:20So let's just zoom in. And I have a folder
1:22here from Data Sources from Tableau 2019.
1:26So I'll just go in and grab Superstore
1:27Sales, which is the standard sort of data
1:29set.
1:30I'll select it. And when it opens a file,
1:34you'll notice that Tableau Prep treats all
1:36data formats in broadly the same way.
1:39If I zoom over here to the left-hand side,
1:41it calls the file or the database you've
1:43connected to a connection at the very top.
1:46And then in Excel, it treats the tabs as
1:49tables, but in other databases, tables
1:52would actually be tables, if that makes
1:54sense.
1:55Now, notice here that this Excel file had
1:57only three tabs, Orders, People, and
2:00Returns.
2:01But what Tableau Prep has also done is it's
2:03looked into the Excel file and it's noticed
2:05that there's a named range in Excel, and it
2:08's actually returned that as a table as well
2:09.
2:10So if you have multiple named ranges in one
2:12sheet, it's actually able to pick those out
2:14too.
2:15Now, if I zoom back out and I just click on
2:17the table that I want to bring in, I can
2:20simply drag Orders onto the view, and that
2:24immediately connects to the file.
2:27Now, when we connect to the file, we have a
2:28range of options.
2:29The first thing you'll see is that on the
2:30right-hand side, you have a list of the
2:32columns, and Tableau Prep looks inside of
2:34each column and gives you a sample of the
2:36values available to you.
2:38You can obviously select all the fields or
2:40just select a few. You can rename things as
2:43well.
2:44All those are titled changes in this
2:45particular format.
2:47So the very first time you connect, you can
2:48start to choose what you want to work with
2:50and what you don't want to work with.
2:52This does not affect the number of rows
2:54going into the flow that you're about to
2:56build.
2:57It's just choosing the columns in your
2:59Excel field.
3:00Now, the other thing to be aware of is that
3:02typically on very large datasets, Tableau
3:05Prep is actually sampling the data, and it
3:07has a default sample amount, but you can
3:09also ask it to use all the data.
3:11This will make sure that all the data is
3:12running through your flow each and every
3:14time, but just be aware that this does have
3:17a performance impact on large datasets.
3:20Lastly, if you've made any changes at this
3:22point, they'll appear here.
3:24So let me just go ahead and do that.
3:26Let's say that I deselect row ID.
3:28You'll see that this appears here on the
3:29left-hand side.
3:31Let's say that I rename this by double-cl
3:33icking on it and saying this should be order
3:37date new.
3:38Let's say for some reason I just wanted to
3:40call it that.
3:41That change appears here on the left-hand
3:42side.
3:43And actually, once a change has appeared on
3:45the left-hand side, you can also click on
3:47it, and it will highlight the column and
3:49essentially almost put it back to a state
3:51where you can edit it immediately.
3:54So this is a really, really powerful way of
3:55working with data.
3:57It's almost entirely visual.
3:59Now, once you've connected to the file, you
4:01can just click on your canvas.
4:03This drop-down menu will disappear, and
4:05then you can add a step and just start
4:06cleaning your data, working with your data
4:08as you normally would.
4:10Now, that was a very simple example.
4:13I connected to just one file.
4:14But let's assume I want to connect to a
4:15couple of files.
4:17Well, I have this window here with some
4:19files.
4:20Now, you'll notice we connected to the
4:22Superstore Sales, the American version.
4:25So what I'm actually going to do is I'm
4:26going to drag in the EU version.
4:28Let's say I want to bring my American
4:29dataset and the EU dataset together.
4:32Well, I can actually just drag that onto
4:33the canvas like so.
4:35And what Tableau Prep will do is it will
4:37process it just like another connection,
4:40and then it will ask me, well, what would I
4:41like to bring in?
4:42And then I can just drag that in again like
4:44so.
4:45So again, the exact same process, the exact
4:47same sort of setup.
4:49Now, let's say I have multiple files.
4:51You'll see here that I have a TDS, an Excel
4:54file, TDS.
4:56Now, TDS is a Tableau data sort of type.
4:59So they are not necessarily the kind of
5:00files we want to drag in.
5:02We actually want to drag in this hyperfile,
5:03which has world indicator.
5:05Now, that's a data format that Tableau Prep
5:07will understand.
5:09So let me just drag a couple of these.
5:12So let's see what happens when I select all
5:14four, three of these files.
5:17So two Excel files and one hyperfile.
5:20If I drag those in, Tableau connects to all
5:22of those files, and it basically puts them
5:24all in the connections window.
5:26Now, if I just minimize this, you'll notice
5:28one thing, though.
5:30The only file that it brought straight into
5:32view here was the Tableau extract, the
5:34world indicators.
5:36And actually, when I click on that in Table
5:39au Prep 2020, it actually highlights the
5:42data source that that's from.
5:44So if I click on orders, you'll see it's
5:46highlighting the second EU file.
5:48If I click on the first one, it's
5:49highlighting that one too.
5:51So it makes it very easy to understand
5:52where your files are coming from.
5:54And again, you get the same sort of
5:56capabilities here in the bottom view.
5:59And if I go down again and I select the
6:01extract file, you get slightly less because
6:03it's a different file type.
6:05Extracts in this particular case just has a
6:07single table.
6:08You can have extracts with multiple tables
6:09in some of the newer versions of Tableau,
6:12so watch out for that.
6:13And again, if you drag that extract on, you
6:15can actually drag it on multiple times.
6:17So even though I've already got it onto the
6:19canvas once, I can drag it on as many times
6:22as I want.
6:23It's fundamentally still connecting to that
6:24file.
6:25And if I click on each of them, it's
6:26highlighting the same one.
6:28So this can be a really, really handy
6:30feature to spot where you've got duplicate
6:32files or files being referenced multiple
6:34times.
6:35And it can really help you sort of clean up
6:37your flow and sort of work with data in a
6:40much, much more enduring way.
6:43OK, I just want to show you one last thing
6:45related to connecting to files.
6:48Let's say that you'd like to trace a column
6:51back to the origin file.
6:53That can be slightly tricky to do,
6:54especially if you're working with
6:55references sort of in Excel where there are
6:58formulas everywhere.
6:59Well, in Tableau Prep, that's much easier.
7:01I'm going to open up a sample flow here
7:03that comes with Tableau Prep.
7:05It's just a Superstore Sales default flow
7:07here.
7:08And then I'm going to click on this green
7:10flow here to clean the data.
7:13Now, as this opens, what I'm going to show
7:16you is how this particular flow highlights
7:19to me where a particular field has come
7:21from.
7:22So as we load this up, if I click on Appro
7:24ver, you'll notice several things.
7:27It's still processing the data, so you can
7:29see these lines kind of going across the
7:30screen, but it's just about finishing.
7:33And if you look below, it's highlighted
7:35this column in blue.
7:37I can also see how it looks like in my data
7:38set.
7:39There's quite a lot of nulls there, and
7:40that sort of correlates with this at the
7:42top.
7:43Now, the thing I want to highlight is if I
7:45wanted to trace where this field was coming
7:47from, all I'd have to do is look up at my
7:50view.
7:51Because if you notice the lines here, if I
7:54just scroll down a little bit, the lines
7:57here have actually gotten darker.
7:59So if I go to the left, you'll see that
8:01this line traces its way back here.
8:04And if I go back out, and let's click on
8:06Days to Ship and just give ourselves a
8:08little bit more space here, you can see
8:10that this column comes from several of our
8:13files.
8:14So if I go back to Approver, you'll see
8:16that this comes from this particular file,
8:19and it also shows me that it goes to this
8:20particular output.
8:22But that's just not it.
8:24You see, this Approver column is not
8:26actually included in my input.
8:29It's actually included in the next step.
8:33And the way I know that is if I click back
8:35on Clean 2, Tableau Prep, when I click on
8:39Approver, actually highlights the steps
8:41that are involved that generate these
8:43particular icons.
8:45If I zoom in, you can see that these three
8:47icons are grayer than normal.
8:50Okay, so it's a very small hint, very
8:52subtle hint, but it's a very useful hint.
8:55And then if I click on this step, I can
8:57then expand this column, and I get another
9:00call out here.
9:02So if again, if I zoom in, I get these
9:04three icons.
9:05Okay.
9:06And then if I click on this column, I get
9:08the same hints again here in gray.
9:11So this field, this field, and this field.
9:14So calculation to split out something from
9:16the notes, it then gets renamed, and then
9:19there's a group and replace to merge some
9:21fields together.
9:22So that's some really, really powerful
9:24capability.
9:25Even in the Summarize view, you get some
9:27detail as to what's going on.
9:29And even without that, you can very simply
9:31just look at your flow and see where a
9:33particular field is coming from.
9:36I head back to my other flow now.
9:38That's pretty much it.
9:39That's how to connect to files, that's how
9:40to add files.
9:41Once again, just make sure you check the
9:43file types that are supported by Tableau
9:45Prep, both in terms of databases and static
9:48files.
9:49And then once you've done that, you nearly
9:51always have the full capability to choose
9:53how you work with your data.
9:55You'll have this pane here on the left-hand
9:57side and the right-hand side, which gives
9:59you a rundown of all the views.
10:01And the left-hand side, which gives you the
10:03ability to change the way you sample and
10:05also gives you a rundown of the changes
10:07that you've made to the file.
10:09Thanks for watching.
10:11If you like the content, please subscribe.
10:13Otherwise, drop a comment below, and I'll
10:14catch you in the next video, where we'll be
10:16talking about, if I just bring the list up,
10:20cleansing data.
10:23Catch you in the next one
Tableau | Data prep