Wildcard union enhancements in Prep - New in Tableau 2022.2

0:00In Tableau 22.2, Tableau Prep now allows

0:03you to use an increased list of file

0:05attributes

0:06to do wildcard unions inside of Tableau

0:08Prep. Let's get stuck in. Okay, so we're

0:11here inside

0:12of Tableau Prep 2022.2. I'm going to go

0:15ahead and connect to a CSV for the first

0:17example.

0:18Let's go ahead and select a text file over

0:20here on the connections pane on the left

0:22hand side,

0:23and for this I've actually got some crime

0:25data and we're going to connect to just one

0:26of these files

0:28and once we've done that we're going to see

0:30what this feature can do for us. Now, when

0:33you connect

0:33to a file in Tableau Desktop, when you

0:35connect to a CSV, typically you might be

0:38used to seeing the

0:39other CSV files that you find here on the

0:41left and you might think that's where you

0:43need to go

0:44to for this particular feature to see how

0:46to union other tables that are essentially

0:48in the same

0:49folder. Instead, you need to go to the

0:51tables tab just over here and select union

0:54multiple files and

0:55that enables this feature and you can see

0:57the new interface that we've got. You've

1:00essentially got

1:01two sets of filters. In this first example

1:03for CSV, we can only really see one of the

1:05filter

1:06types, that's file filters and that's

1:07essentially what this feature is about.

1:09Essentially, this

1:10filtering area allows you to add a criteria

1:13to tell Tableau Prep which of these CSVs

1:16you want

1:17to bring in. So let's start by playing

1:19around with this. First of all, let's go

1:21ahead and select

1:22add file filter and you can see that I get

1:25five criteria; file name, file size,

1:28creation date and

1:28modification date. The other criteria that

1:31we get, that's why I said five, is actually

1:34where to search

1:35up here. So those are essentially the

1:37criteria. You've got the four file

1:38attributes as well as the

1:40folder in which you're searching and that

1:42makes five. Now for each of these, you do

1:44get a subset

1:44as well. So for example, if we go back here

1:47to file size, I can do it by range or by

1:49rank. I can

1:50do it by creation range, relative and rank

1:52and same with modification date. So

1:54essentially, there's a

1:55type of filter type for each particular

1:58data attribute. So file size is just a

2:01range because

2:02it's looking at the size and dates have

2:04ranges relative and rank because you can

2:07sort using the

2:08date as well. So let's also look here at

2:10the search box at the top because when I

2:13select the

2:14search in folder, it actually shows me like

2:17a hierarchy of the folder structure that I

2:19'm actually

2:20currently in. So if I select crime, which

2:23is the subfolder I'm in, that crime folder

2:25is inside a

2:26data folder called prep sample data, which

2:29is in a folder called 2022-2 demo, which is

2:32on my desktop,

2:33which is on my computer, which is owned by

2:36me. So this criteria allows you to sort of

2:39go up and down

2:40the directory and really sort of customize

2:42things. The further up you go, the more of

2:44your computer

2:44you're searching. And in essence, the more

2:47trouble you could get into if you tell

2:49Tableau Prep to

2:50search all subfolders. I'll stick it to the

2:52crime folder now. And let's just have a

2:54look at our first

2:55filter. We'll call this file name and I'll

2:57show you how that works. And the file name

3:00filter has

3:00a match criteria at the top. So you can

3:02either find something that matches or doesn

3:05't match. So

3:06it allows you to search in both ways,

3:07define what you're looking for or define

3:09what you want to

3:10avoid. And that way it will pick up

3:12anything that matches that. So let's look

3:15at matches. And what

3:16we can see here is we've got a file that

3:18talks about crime data. And in essence, it

3:21's got a

3:21specific sort of naming structure for

3:23different types of files in different areas

3:26. So let's say

3:26I wanted to go and find all the historical

3:30crime data. There's essentially two here,

3:33one by LSOA

3:34and one by Ward. And so you might wonder,

3:36well, how do I just go find just the

3:38historical data

3:39files and look at them? I don't know if

3:41whether they're in the same data structure,

3:44I don't know

3:44anything about them, but I just want to

3:46bring them all together. Well, for this one

3:48, what I would

3:49probably do is type a star, then I would

3:52type historical or just historic, actually

3:55historic,

3:56and then enter another star. And what you

3:59're basically saying is go and find anything

4:01that has a term historic anywhere in the

4:04file name. And if I hit enter, you'll see

4:06that it

4:07returns those three files. And this one has

4:10historical, this one has historical,

4:13this one has historic. That's why I typed

4:15historic. If I'd typed historical, I would

4:18not

4:19have necessarily got back that last one. So

4:21let's go ahead and enter. And you can see

4:22the last one

4:23doesn't match, because the term it's

4:25looking for is not historic, it's

4:27historical. And that

4:28was only, you know, referencing historic.

4:31So that's how this files criteria works. In

4:34something

4:35like Alteryx, you're also able to say, look

4:37, go and get every CSV by just putting star.

4:40csv.

4:40In this particular case, I could just give

4:42it that criteria, I could just say star.csv

4:44,

4:44and hit enter, and it would show me

4:46everything in the folder. But that's

4:49basically as good as just

4:50not having a search criteria. So you don't

4:53need to do that, you can just go ahead,

4:55look at the

4:55folder. And if you're happy with everything

4:57, you can just hit apply, and it will go and

4:58pull

4:58everything in. Now, the thing to remember

5:00here is you're unioning these files. So you

5:03are going to

5:03need to make sure you've got a common data

5:05structure between them. Otherwise, you're

5:08going

5:08to create yourself a bit of a challenge

5:10inside of Tableau Prep to clean this up and

5:11make it work

5:12really, really well. So that's the first

5:14filter. That's a file name filter. The file

5:16size filter

5:17is really, really good. Again, this isn't

5:19rocket science, when you choose range of

5:21sizes, you also

5:22get the ability to use less than or less

5:24than or equal to, greater than or greater

5:26than or equal to.

5:27That's just part and parcel of it. What I

5:31like about this filter is that they do

5:33correctly

5:34sort of aggregate up the file specification

5:37for you. So kilobytes to megabytes to gig

5:39abytes to

5:40terabytes. But weirdly, when they show you

5:42the size down here, it's only shown in

5:44bytes. So I'm

5:45thinking this is a bug. This is something

5:47to do with launch day. If not, this would

5:49be a really

5:49nice sort of error of improvement. I can't

5:52do bytes to kilobytes to megabytes to gig

5:54abyte

5:55conversions in my head, specifically

5:57because on some computers, let's say if you

5:59're on a Mac,

6:00it counts megabytes and gigabytes

6:02differently to how a Windows computer

6:04counts it, which is

6:05in multiples of 1024. So it's just

6:08something to kind of bear in mind that this

6:10would be a really

6:11nice sort of thing to use. But nonetheless,

6:14the search criteria is pretty flexible, you

6:16can go

6:17ahead and say, "Look, bring me back

6:19anything that is going to be at the moment,

6:21it's zero to one

6:22terabytes." Again, I would love this to pre

6:24-select the largest file size that it can

6:26see. So for

6:27example, here, if I say in megabytes, let's

6:31say, "Give me back everything that is up to

6:3430 megabytes."

6:35Hit enter, you'll see that everything up to

6:3730 comes back. And it's quite dynamic, it's

6:39quite

6:39real. As you type, it doesn't update in

6:41real time, but when you hit enter, it

6:44updates again,

6:44and you can see sort of files sort of

6:46dropping off as I do that as well. So that

6:49's the criteria for

6:52file size. Let's go ahead and delete that.

6:54Add another one, creation date and

6:55modification date.

6:57These are just date ranges. They'll both do

6:58the same thing. So I'll just show you how

7:00one of them

7:00works. Range of dates allows you to pick a

7:03start date and an end date. Again, I don't

7:05understand

7:06why this starts at 1972. The hack here is

7:09not to do the drop down and then start sort

7:11of clicking

7:12through. You might come here and try and

7:14click on this to type it. You can't. The

7:16trick here is to

7:17just type the date in here if it's going to

7:19be faster. So you can just go ahead 2021,

7:22and then

7:22you start from a much, much more reasonable

7:24sort of timeframe, and you can get going.

7:27The table

7:28here gives you a high level overview of

7:30those dates if you want to be able to

7:31filter. So that's

7:32really, really nice. But in essence, you

7:35can go ahead and choose whatever search

7:37criteria works

7:38for you. Just remember that everything that

7:41gets found here is always coming back. When

7:44you go

7:44ahead and hit apply, this will essentially

7:46pull everything in the view back into your

7:49prep flow,

7:50as well as the file that you basically

7:52selected. So that's a really nice touch, a

7:56really nice way

7:56of working. Now, the other thing you can do

7:59, and it's probably not as obvious, and this

8:02is probably

8:02one of those places where this little left

8:04hand side interface is a little bit small

8:06now. So what

8:07I might do is expand it a lot larger. I don

8:10't think I can change the middle table

8:12height. I'd

8:13love to sort of move that down, but it

8:15feels a little bit cramped. So one trick I

8:17do is I just

8:18minimize Tableau prep by hitting control

8:20minus or command minus on a Mac. What that

8:23does is because

8:24Tableau prep is a web application, it

8:25treats it like a browser, and it just gives

8:27you this sort of

8:29illusion of creating more space because it

8:30makes everything smaller and spreads it out

8:32. So now it

8:33looks like I'm in a higher resolution

8:35screen, which isn't probably good for this

8:37video.

8:37And you following along, but hopefully now

8:39you can see I've got a bit more space. I

8:42can go back

8:42to that more compressed view and I can

8:44continue to add more filters, whether it's

8:46by file name

8:47or creation date. And you can see these are

8:50just stacking up. And as they stack up, you

8:53basically

8:54reduce the list, a list in this case, to

8:56nothing. So nothing will come through. But

9:00nonetheless,

9:00that's how it works. Okay. So I think I've

9:02really labored that point really, really

9:05hard. That was

9:05the CSV. Let me show you what happens when

9:07we connect to an Excel file. Okay, so let's

9:10connect

9:10to an Excel file. I'll go back here to the

9:12top, I'll hit plus, and we'll go ahead and

9:15select a

9:15Microsoft Excel file. And when we do this,

9:17let's just go back one. I think we need to

9:19go back to

9:20the prep sample data. And in this

9:22particular case, I've got three files, Last

9:25Efem Data, Bookshop,

9:26and Sample Superstore Sales. I'm going to

9:29select Sample Superstore Sales for this

9:31particular one,

9:32not the data everyone loves, but I'm going

9:34to drag it in anyway. And we're going to

9:36connect to it,

9:36go to tables, go to the same option, select

9:39union multiple tables. And now you can see

9:42the options that we have. Let me make this

9:43larger again. Now, the reason I want to

9:46show you this is

9:47because an Excel file has an additional

9:49search criteria that other files won't. So

9:53here you can

9:53see there's a worksheet filter as well as a

9:56file filter. So the file filters are what I

9:58've showed

9:59you already, file name, file size, created,

10:01modified date, all that jazz. Worksheet

10:04filters,

10:05specifically for Excel files, allow you to

10:08also search a criteria for worksheet naming

10:10. And in

10:11this particular case, this will only have

10:13the worksheet name in this little dropdown.

10:16So I can

10:17then go ahead and put in specific workshe

10:18ets. So if I just want to bring in the

10:20orders table,

10:21I can just go ahead and type that and it

10:23will only find the orders table in any

10:25worksheet if it's

10:26included in that Excel file. Okay. Now, the

10:29other thing this did when we came to this

10:31option,

10:32when you connect to an Excel file, this is

10:34going to, I think, catch a few people out,

10:36is that it actually prefilled the file name

10:39options here. So at the moment, what we're

10:41looking at is just one file. And you might

10:44think, "Oh, I can't go and look and open

10:46seven Excel

10:47files at the same time." That's actually

10:50not true. If you go ahead and clear this

10:52file search filter,

10:53you'll see that it thinks about it for a

10:55little bit longer because what it now has

10:57to do is go

10:57into the directory and file every Excel

10:59file. And once it's found every Excel file,

11:02look at

11:02every sheet in every Excel file, then pull

11:04through the data. And that's what you see

11:07here. You can

11:07actually pull through the different sheets

11:09inside of multiple Excel files. And that's

11:12really, really

11:12nice. So you can see here, I have some data

11:15for this bookshop and it's actually got

11:18sales Q1,

11:19Q2, and Q3, and Q4. That's actually what I

11:22'm looking for. So let's go ahead and add a

11:24worksheet

11:25name criteria. And we'll just say anything

11:28that starts with a capital sales and ends

11:31however we

11:32want and hit search. And this is a little

11:35bit slower because I think it has to

11:38dynamically

11:39search those files again and again and

11:40again. And now you can see I've pulled the

11:42sales data from

11:44the beta bookshop, even though I connected

11:46to the sample superstore sales. And now it

11:49's going to be

11:50able to work. So let's hit apply. And once

11:52we've hit apply, it's going to go ahead and

11:55bring all

11:55those in. And you'll see Tableau Prep

11:57update here on the right hand side. And now

11:59we've just got the

12:00sales and it's sort of pruned everything

12:02down. And when we go ahead and look at the

12:04orders,

12:05now it's connecting to that beta bookshop

12:06file, even though I'm connected to sample

12:08superstore

12:09sales. It's a really, really weird sort of

12:13scenario to be in. I have found that this

12:15takes a little

12:16bit longer because again, it's having to do

12:19this by talking to the files. And it does

12:21what you'd

12:22expect Prep to do, which is to give you the

12:24table names and the file that the file is

12:26coming from.

12:27So this is something that we'll do every

12:29single time. Even if it's a CSV, it will

12:32tell you the

12:32table name, which is essentially going to

12:34be the file for CSV. But for this in Excel,

12:37you're going

12:37to get all the different file names if you

12:39do connect to multiple files over here on

12:42the right

12:42hand side. So that's a really, really nice

12:44touch. Now let's say you want to connect to

12:46a database.

12:46I've actually jumped ahead here and I've

12:48connected to my Snowflake instance. You

12:51might think you get

12:52these abilities with tables in a database

12:54like this. So you might want to maybe

12:56dynamically

12:57search for multiple tables like here, union

12:59them together and bring them in. This is

13:01actually not a

13:02currently supported feature. So this

13:05feature only works for CSV files, Excel

13:08files and statistic

13:09files. Those are basically the only file

13:11types that this will work for. Hopefully in

13:13the future,

13:13you get this capability with other tables

13:15inside of a database, but it's

13:17predominantly targeted at

13:19flat files essentially. So that's going to

13:21be the only parameters where this works. It

13:23won't work

13:24as you think with a database here. I'm

13:26connected to Snowflake with some sample

13:29data and you see,

13:30I don't get any option to bring it in. I

13:33just get the old sort of sampling method

13:37capabilities and

13:38I can sort of make some changes to this

13:40particular window here on the right hand

13:43side. But in terms

13:44of settings, this is really all I'm getting

13:46. I don't have much more than just the

13:48ability to

13:49select one thing at a time. And so that's

13:51pretty much it for this particular feature

13:53with databases.

13:54So unfortunately not much to add. A couple

13:57of things just to be sort of aware of. When

14:00you do

14:00connect to something using this feature and

14:02you get it working and everything's good,

14:05you do get

14:05this little plus icon that lets you know

14:07that that is actually bringing in multiple

14:09tables and

14:10unioning them together. You don't get that

14:13if you don't do that. So just as a simple

14:15example,

14:16if I select on this file and I was to go

14:18back up to the top and I was to select a

14:21single table,

14:22you'll see that the icon disappears. And if

14:25I go ahead and select that option there,

14:28only once I've

14:28actually successfully hit apply, does the

14:32icon appear here for you to see. So that's

14:35an icon

14:36that only happens again when you

14:37successfully complete the operation. Nice

14:39little quirk to be

14:40aware of. Another thing I just want to

14:42highlight is that it's actually possible to

14:44, with this

14:45feature, connect to one file but pull in

14:47data from a completely different file,

14:49which makes

14:50troubleshooting a little bit more tricky. I

14:52've actually already done that in front of

14:54you and

14:54you maybe didn't realize. You can see here

14:56that I actually originally connected to the

14:58sample

14:59EU Superstore Excel file. But then inside

15:02of this search criteria, I actually went

15:04and pulled the

15:05files from the bookshop.xlx file, which is

15:08in the same folder as the Superstore files.

15:11And then I

15:12pulled in cells, the Q1, Q2, Q3, Q4. That's

15:15where that was actually coming from. And so

15:17if you go

15:17look at the prep output and actually look

15:20at this, you'll see that there's nothing

15:23coming from

15:24my Superstore cells. You can actually see

15:26the bookshop data here just above my head.

15:28It's the only file that's coming in. So

15:30that to me is like one of those weird mindm

15:34azes, right?

15:35Like how is it that you can connect to a

15:36file that you never intended to connect to

15:38in the

15:38first place? Well, this sort of scenario

15:40exists. And so if you're having to troubles

15:42hoot your prep

15:43flow and this is what's going on, this is

15:45exactly what's happened. Essentially,

15:47someone's connected

15:47to one file, then decided to pull something

15:50from another file. And that's basically

15:52where

15:52it's going. Now, what is interesting about

15:55this is if I go in and edit, what I'm not

15:57sure about

15:58is whether it's going to be expecting the

16:00beta bookshop.xlx or the sample EU Super

16:03store store.xlx.

16:05In essence, what I'm going to do now is I'm

16:07going to actually switch the connection and

16:09see what

16:10that does. So if I double click on that,

16:12and we'll let it sort of do its thing, I

16:15think the search

16:16criteria will still apply itself to that.

16:19And you can actually see that it's actually

16:21working as you

16:22would expect. It's intelligent. You can see

16:25it's highlighting, first of all, the four

16:28things that

16:28are pulling through, even though I wasn't

16:30connected to that in the first place. And

16:32now this is still

16:34obviously running completely fine. So it's

16:36good that it degrades beautifully, and it

16:39still shows

16:40you what it's supposed to do. If you're

16:42just not connected to the specific file in

16:44question,

16:45you won't see that over here on the left-

16:47hand side. I guess that's the sort of

16:49downfall of this.

16:50Connect to the right file, you'll see the

16:51right things. If you don't connect to the

16:53right file,

16:54subsequently pull in something from another

16:56file, then you won't see it here on the

16:58left

16:58when you really should because you want to

17:00know that cells Q1, Q2, Q3, and Q4 are

17:03being brought

17:04into the flow as well. And that's also a

17:05nice touch. I don't believe I saw that

17:07previously with

17:08the Excel files because again, we didn't

17:10see this tabular view. But again, if you're

17:12using Excel,

17:13it's really nice that you get these four

17:15files here highlighted in blue to tell you

17:18that they're

17:18actually being used in the flow. So that

17:20was another quirk that I sort of discovered

17:22. I

17:24didn't really know where in the demo to

17:25show you this, but definitely something to

17:27highlight

17:28in this video. Yeah, this is it. This is

17:30the feature pretty much in a nutshell. Let

17:34me know

17:34what you think of this. I think this is a

17:36long time coming. This is one of those

17:38features where

17:38you see it and you're like, "About time.

17:41Thank God." It's one of those kinds of

17:43features that

17:43just makes that little bit easy to work

17:46with specific use cases where you have to

17:48handle

17:49a lot of unwieldy files. Together with some

17:51of the previous changes as well, you know,

17:54prefixes and suffixes as well, it's just

17:56starting to feel like Tableau Prep is now

17:58starting to get

17:59into its stride and can be seriously

18:01considered for at least light to medium

18:04data prep. Nothing

18:05too heavy just yet. All the heavy stuff I'd

18:08still do and all tricks. But a lot of the

18:10time,

18:10I am starting off in Tableau Prep and it's

18:13doing the job perfectly fine. In fact, it's

18:15a lot nicer

18:16to use than other tools as well that I'm

18:18comfortable with. So I'm actually quite

18:20enjoying

18:20it. Let me know what you think of the

18:21feature below. Let me know in the comments.

18:23Let me know

18:24what you'd like to see done with this

18:26feature. Let me know what ideas you come up

18:28with. It's

18:29always great to have people watching the

18:31videos comment, give us use cases and share

18:33their ideas

18:34with others. Sometimes even I forget things

18:36as well. So that's a really nice way to add

18:38to the

18:39discussion. Thanks for watching and I'll

18:41catch you in the next video.

18:42[0-1]

18:43[0-1]

18:44[0-1]

18:45[0-1]

18:46[ Silence ]