Tableau Prep Builder: Connecting to files (Part 1 of 7)

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