Append All Files in a Folder - Preparation

Quick reference

• 00:04 In this video,
• 00:05 we're going to get prepared in order to append all files in the folder.
• 00:09 So what this means is we're gonna start from a virtually blank workbook,
• 00:14 like you see here.
• 00:15 And what we're gonna do is we're gonna point to the actual folder and
• 00:20 get a list of the files that we need to do,
• 00:22 setting up the queries following the best practices.
• 00:25 So to start with, we're gonna go and create a new query.
• 00:29 This time we're gonna choose to go from file.
• 00:31 And rather than from a specific type of file, we're gonna choose from folder.
• 00:36 We're gonna import a folder full of CSV files.
• 00:41 You’ll notice that we get the folder path to browse for.
• 00:43 So I’m gonna click Browse.
• 00:45 And we’re gonna drill in to this PC and my WIndows C drive.
• 00:50 And I’m gonna go to my GoSkills data, into the append files and folder.
• 00:55 And we’re gonna use the Begin folder.
• 00:58 And now we’re gonna say OK.
• 01:01 And then we'll say once again, OK.
• 01:05 Now this will open Power Query to a preview window which shows a listing of
• 01:08 all of the different files inside the folder.
• 01:12 Now, we have the option, just hit the Combine button.
• 01:14 I'm gonna suggest that you never, ever do this, like I'm gonna suggest that you
• 01:18 never hit the Load button unless all you want is a list of the files in the folder.
• 01:24 Like normal, we're gonna click Edit and go into the Power Query editor.
• 01:28 And we're gonna start setting things up the way that we need them to be.
• 01:32 The first thing we're gonna do is we're gonna go and
• 01:34 give our query a name, something along the name of files list.
• 01:38 I like this one because it gives me a nice little list that gives me
• 01:42 all of the files in the folder, which you can see right now only actually has one.
• 01:48 You'll notice in here that we have a couple of different green keywords.
• 01:51 We've got binary, and again, don't click it or else it will draw right in and
• 01:54 it will blow the whole thing up.
• 01:56 So what we wanna do is click in the white space and you'll see that all we get is
• 02:00 a little picture of the CSV file, which really isn't all that useful at all.
• 02:05 We have the file name.
• 02:06 If I just expand that a little bit,
• 02:08 we can see that it is a CSV file with the extension.
• 02:11 We've got the date that it was last accessed, when it was modified, and
• 02:14 when it was created.
• 02:16 Just as a bit of an amusement thing here, sometimes,
• 02:18 we can see files which were actually created
• 02:22 way later than the last modification date, which is always kind of amusing.
• 02:26 But these columns could be very, very useful
• 02:29 if you're trying to actually figure out which is the most recent file on a list.
• 02:33 You've got three different columns there to play with.
• 02:36 We have an Attributes column with a record, and
• 02:39 this gives you all of the different windows control pieces around these files.
• 02:44 Is it a system file, a directory file, and whatnot?
• 02:47 And naturally with the little expansion arrow, we could, if we wanted to,
• 02:51 open up and actually pick out some of these things if they were important to us.
• 02:55 In this case, they're not, so we're just gonna hit Cancel.
• 02:59 And finally down the very end here you'll notice folder path.
• 03:01 Now I'm gonna select this guy here.
• 03:03 You'll notice he's not green, so if I click on it,
• 03:06 it just gives me a preview down at the bottom.
• 03:08 A green word will drill in, a regular word will not.
• 03:12 Now the key that I wanna do to prepare here is twofold.
• 03:15 Number one, I want to filter down to a list of CSV files,
• 03:21 just in case someone throws an Excel file in here that would blow up everything.
• 03:26 So in order to do that, this is where I actually have to get to step 1a.
• 03:32 And the problem is this, the extension.
• 03:35 Power Query is case sensitive, which means that if somebody puts in a capital CSV
• 03:40 file and I just filter to just lowercase csv files, at that point,
• 03:44 I'm gonna have a problem because we get rid of the capitalized ones.
• 03:47 So what I'm going to do is I'm going to right-click on the Extension column and
• 03:51 I'm going to go and transform this to force it into lowercase.
• 03:57 This way I can be assured that no matter what way the Caps Lock
• 04:00 key is toggled on the person's keyboard when they create the file, I
• 04:03 will always be able to make a filter that picks up all of those common file types.
• 04:08 Now the next thing that I wanna do is I wanna filter just to CSV files.
• 04:13 So I'm gonna click the little filter arrow.
• 04:15 And you'll notice that it's checked Select All in CSV.
• 04:18 So I'm gonna uncheck Select All, and it unchecks everybody.
• 04:21 I'm gonna check CSV, no, it's rechecking the Select All,
• 04:25 which is kind of frustrating.
• 04:26 So I can't use a filter here and remove something and
• 04:30 say force it to just this type.
• 04:32 And that's why I have this for text filters, where I can actually go in and
• 04:36 set a manual filter.
• 04:39 And what we're gonna do is we're gonna set the manual filter where
• 04:43 our file extension equals .csv.
• 04:47 Don't forget the dot, and make sure it's all lowercase,
• 04:49 because we've already forced it to lowercase.
• 04:52 We're now gonna say, okay.
• 04:54 It doesn't look like anything's different, although you will notice that you've
• 04:57 got a filter icon that is actually showing up on the left-hand side here, or
• 05:01 our right-hand side, rather.
• 05:02 So that tells us that the filter's been set.
• 05:05 And at this point, the only thing that I'm gonna do is I'm gonna go and
• 05:08 hit the bottom half of Close and Load.
• 05:10 We are going to take the Close and Load too and we are gonna create this guy here
• 05:15 as a connection only query, and we're gonna say OK.
• 05:20 From this point forward, whenever you work with this thing,
• 05:23 we know that we can always come back, edit this query.
• 05:28 We can go back to the source step, and
• 05:30 we can change the file path either in the formula bar or by hitting the gear icon.
• 05:35 And this is the only place that it will ever need to be changed,
• 05:38 providing we reference the files list query going forward.
• 05:42 And we'll take care of that in the next video.

Lesson notes are only available for subscribers.