Write to Excel in Tableau Prep : New in Tableau 2021.1

0:00Hey it's Tim here, in today's video we're

0:01going through the new features in Tableau

0:03Prep 2021.1.

0:05Now Tableau Prep is sort of sneaky because

0:07they actually update it every single month

0:09with new features and so it sort of doesn't

0:11really follow the traditional release cycle

0:14that we're

0:14used to seeing for desktop and server. So

0:16that's why it says here Tableau 2021.1.2

0:20because this

0:20is actually the second release of a 2021.1

0:23release for Tableau Prep. Now there's only

0:26two things to

0:26really talk about here, I'm looking at

0:28Tableau's own documentation if you don't

0:29know how to get to

0:30this page check out my other videos on how

0:32to find out all the new features in Tableau

0:34.

0:34One thing they did is they removed the

0:37driver for Amazon Redshift so if you're

0:40installing Tableau

0:40Prep for the first time and you use Amazon

0:43Redshift it's actually been removed as part

0:46of the default installation process. If

0:48that's something that you use you're going

0:49to need to go

0:50and install that driver yourself. They have

0:52a link to the downloads page which Tableau

0:54have on a whole

0:55load of databases so that's the best place

0:57to go. Now the other new feature they added

0:59is the ability

1:00to write to excel. Now when I first saw

1:02this I got really excited because I have

1:05expectations coming

1:06from other tools like Alteryx and so I sort

1:08of just jumped right in and I tried to do a

1:10couple

1:11of things and actually a few things caught

1:12me out. So in this video I'm going to be

1:14covering those

1:15things and we're going to sort of look at

1:16some of the exceptions as well that to be

1:18aware of.

1:18So first of all let's get into Tableau Prep

1:20and let's just start creating a flow.

1:23Actually in fact

1:23what we're going to do is use one of the

1:25sample flows because we don't need to

1:26really go through

1:27the process of building the flow we can

1:29just use one of these sample flows and

1:30change the outputs

1:31to suit our needs. So in this flow we have

1:34a basic set of data coming from different

1:37regions

1:37essentially being aligned clean together

1:39then it's being unioned and then we have

1:42the returns data

1:42that's being joined on a little bit further

1:45down and then there's a couple bit there's

1:46a bit more

1:47sort of data prep going on here in clean

1:50set number two. We then have quotas and

1:52targets and then

1:53essentially we have some sort of output

1:56here that sort of analyzes the output. So

1:58this is a very

1:59simple flow but it covers sort of the

2:00traditional things you might do in the

2:02business when you're

2:03collecting data from different regions and

2:05different teams. Now one of the things I'd

2:07love to do is export this back out to excel

2:09up until today or up until 2021 was

2:12released.

2:13When we used the file output the only file

2:16options we got was a Tableau hyper file and

2:19a CSV and now

2:20you can see here if I just highlight where

2:22I'm looking you can see here that we now

2:23have three

2:24files so essentially we have excel and a

2:26comma separated values file and an extract

2:29file which

2:30is a dot hyper okay so that's really good

2:32to have those options now it just saves you

2:33having to

2:34add that additional step of opening a CSV

2:36in excel and then putting everything into

2:38the sheet.

2:39But let's go ahead and let's just try and

2:41set this up so first of all I'm going to

2:43change this

2:44particular output to write to excel so what

2:46we'll do is I'll change the location to

2:48somewhere that's

2:49easy to find on my machine I'll go to my

2:50desktop and I'll select this folder which

2:52is called output

2:53to prep click accept and there we go we're

2:55pretty much ready to go now the thing we

2:57need to do is

2:58change this to an excel file so I'll click

3:01xls and then it'll ask us which worksheet

3:03now because

3:04there's no excel file in that folder at the

3:06moment there's no worksheet to choose so I

3:08'll just call

3:08this sheet one as you do in Tableau and

3:11then I'll say create a new worksheet okay I

3:13'll select create

3:14table okay and don't forget these options

3:17work across different data sources so when

3:19you create

3:19a table it gets rid of the one that's there

3:22and puts a new one and append will simply

3:24add data

3:24to the bottom of the existing data set and

3:26replace is like a hybrid essentially what

3:28they're going to

3:29do is if the table exists it's going to

3:30replace the data that's in there entirely

3:33but if it doesn't

3:33exist it's going to create it so create

3:35table sort of creates it every single time

3:38and replace will do

3:39sort of a hybrid of create and replace at

3:41the same time if that makes sense okay so

3:44in this case

3:45we're going to choose create or try replace

3:47a little later on so you can see the

3:48behavior

3:49and that's it we've pretty much got our

3:51standard example there and I'm just going

3:53to hit run flow

3:54just so you can see this working so here we

3:56are outputting to superstore sales xlsx and

3:59as the

4:00output so you even get a little dynamic

4:01counter that shows you the rows being

4:03output now what I

4:04will do is I will open up my finder window

4:06just so that we can get to this folder here

4:09so if I just

4:10do this and let's go to a list view so we

4:12can just see the file there so superstore

4:14sales

4:15xlx let's open that file now that it's been

4:17written and it's finished in the background

4:19you can just see that it says done there

4:21and if I bring that back into my window you

4:23'll see

4:24that we have it in an excel file which is

4:26great we've got sheet one as I named it and

4:28that's

4:28that's that's working great now what I'm

4:30going to do is I'm just going to open up

4:32another sheet

4:32and I'm going to call this sheet two okay

4:35and notice I've created this in excel

4:37rather than

4:38you know in in tableau prep so we're just

4:40going to leave that as that and then we're

4:42just going

4:43to hit save and now that's ready to go and

4:45now we're going to close excel and we're

4:48going to

4:49close this window here and close this

4:51window there and make sure that's done and

4:53what we're going to

4:53do is we're going to set the second table

4:55to look for that second sheet so let's go

4:58ahead again and

4:59go back to my desktop select the output

5:01prep folder and I'm going to actually

5:04select this

5:04folder and hit ok you can't actually select

5:07the file because this is the option asking

5:09you where

5:09you'd like to save the file it's not the

5:11option asking you what file you'd like to

5:13save so hit

5:14accept then down here you get to choose the

5:17file type so xlsx okay so we're now pretty

5:20much now

5:20ready to go we've got superstore sales xlxx

5:23now when I click on this drop down you can

5:26see that

5:26I can search here and I can I can try

5:28typing sheet two and I need to make sure

5:31that my excel file is

5:33actually closed so I'm going to close excel

5:36entirely and I'm just going to select I'm

5:38going

5:38to try and type in sheet two here and just

5:41see if that works and it says create a new

5:43worksheet so

5:44what I'm not going to do is I'm not going

5:46to create that I'm going to just create a

5:47new worksheet

5:48called sheet three okay so now we have a

5:50new worksheet called sheet three and this

5:53is where

5:53I thought the options would allow me to do

5:55something sort of different so you can see

5:58here that the append to field options is

6:01selected we're going to ask you to create a

6:04table the

6:05reason I've done that is because if I

6:06append to table it's going to look for the

6:08table and there's

6:09no sheet three there so you can see here in

6:11the middle that prep is telling you that

6:13there's no

6:14match so the field is ignored so this would

6:16actually output nothing if I switch over

6:18here to

6:19create a table you'll see that it's

6:21basically going to go ahead and assume that

6:23these are the

6:24fields and columns that I'd like in my data

6:26set so it's going to create them for me and

6:28replace

6:29will do the same thing let's go ahead and

6:30select replace and you'll see here that

6:32actually replace

6:33wants the fields to be there but in essence

6:36I'm hoping it would create the field

6:38because that's

6:38that's sort of what it says it says if the

6:40table doesn't exist it's created when the

6:42flow is first

6:43run okay so we can choose create or replace

6:46but in this case I'm going to choose create

6:48just so that

6:49we have a nice sort of standard data set

6:50and so now what we're doing is we're

6:52running the annual

6:53figures here we're just going to hit run

6:55flow and we're going to see what happens

6:56now you'll see this

6:57outputs with the same names already exist

7:00do you want to replace them so

7:02interestingly it's open

7:04up the file and it's basically saying look

7:06there's already a sheet three before I run

7:08this file let's

7:09go look at that folder and just see what it

7:11actually says so let's go in here and open

7:13up this file

7:14and we'll just minimize this a little bit

7:16and wait for this excel file to open and we

7:18'll check what's

7:19actually in the file because we haven't run

7:21this flow so you put out this data yet and

7:23so you'll

7:23see here that there is there is no sheet

7:26three here so it's slightly weird how this

7:29is working

7:29it's definitely the right location it's

7:31definitely the right file and for some

7:33reason it thinks there's

7:34a sheet three even though it hasn't really

7:36been created so I just wanted to show you

7:38that just

7:38before we know we went anywhere else okay

7:41so now you've seen me run the separate

7:43flows now what

7:44we're going to do is run them both at the

7:45same time so you can see here we've set

7:47this first

7:48one up we've set the second one up now let

7:49's go ahead and run this through properly so

7:51if I just

7:52hit this top play button it's actually

7:54going to run through both files so let's go

7:56ahead and do

7:57that you'll see here that it says running

7:59query generating rows and we'll wait for it

8:01I have excel

8:02open I don't think I have the file open

8:04though so this should complete correct it's

8:06completing

8:06perfectly fine let's wait for this to

8:08finish and this one is probably taking a

8:11little longer

8:12because it's a little bit more rows so hit

8:14done so let's go back to our excel file and

8:17double click

8:18on that and what we should see is three

8:20sheets sheet two should remain untouched

8:22because this

8:23is the one that it didn't see uh tableau

8:25prep wasn't seeing this field when I was

8:28searching for

8:28it uh sheet three which is the aggregated

8:31view and then sheet one which is the full

8:34view of all our

8:35sales which has basically been recreated so

8:37you can see the list writing to both files

8:39really

8:39really nicely now I have to be honest there

8:41's a there's a few things I have to sort of

8:43mention

8:43here because the first time I tried this I

8:45had a little bit of a quirk where it wasn't

8:48doing both

8:48files at the same time so um this behavior

8:52I think can only be either a bug or some

8:54sort of exception

8:55but I was only able to make it happen twice

8:57and then when I've tried to recreate the

8:59scenario I

9:00thought was causing it it just didn't

9:02happen again so definitely something to be

9:03aware of if it's not

9:04working correctly you know what close prep

9:07open up prep again and just try and see if

9:08that works

9:09properly the other thing to make sure you

9:11do is you set up these options here on the

9:13left hand side

9:14correctly okay so you need to make sure

9:16that you're really really paying attention

9:18to what's going on

9:19here let's say you created the same excel

9:21file but instead you have them in two

9:23separate folders

9:24you're going to get two separate files okay

9:26it's not going to create them in the same

9:27place so if I

9:29point this location to one place and then

9:30in the other output I point it to another

9:32place you'll

9:33get two files called the same thing with

9:35sheets in them called sheet one and sheet

9:38three but there'll

9:39be two separate files in different places

9:41okay so that's just something to be aware

9:42of and something

9:43to look out for so it's a nice quality of

9:45life improvement if you're using excel a

9:47lot you need

9:47to be sending excel files to people and you

9:50don't want to be sending csv's and this is

9:52a nice uh

9:52sort of way around that now there are some

9:55uh issues with excel it does have a row

9:57limit if

9:58you're using excel in a sort of desktop

10:00environment so if you're sending lots and

10:02lots of data in a

10:03you know in a flexible way and the csv is

10:05typically the way you get around that limit

10:07then csv will

10:08still become useful for you but otherwise

10:10excel is a really nice quality of life

10:11improvement this is

10:12a useful uh thing if you are maybe just

10:14creating reports that don't need visual

10:16izations you just

10:18need numbers on it on the table and you

10:19just need it done quickly prep is a great

10:21way of doing that

10:22and you can sort of start creating these

10:24sheets and sending them out to your

10:25colleagues so they

10:26can then do their own analysis on it if

10:27that's the case but ideally you'd want to

10:29be doing that

10:30inside of the tableau ecosystem okay thanks

10:32very much for watching this video and i'll

10:34catch you

10:34the next one and be sure to check out my

10:36website we've got lots and lots of videos

10:38on things like

10:39tableau functions on the home page you've

10:42got the 2021 playlist sort of as a headline

10:45item you can

10:45see all the other videos on the features

10:47that i've already made and always be sure

10:49to go out to the

10:50playlist page where you can see some of the

10:52other playlists that i've already created

10:53on youtube

10:54but now also live on this site covering

10:56lots of features across lots of different

10:58capabilities in the tableau platform so be

11:01sure to check it out

11:02until the next video thank you very much

11:08and i'll catch you in the next one