Locked lesson.
About this lesson
Before you unleash this magic skill, there is a bit of prep-work that should be done to future-proof your solution and make it more portable. In this lesson we will explore how to do this.
Exercise files
Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.
Append all files in a folder - Preparation.xlsx19.7 KB Append all files in a folder - Preparation - Completed.xlsx
21.2 KB
Quick reference
Append All Files in a Folder - Preparation
Preparing to append all files in a folder into a single query.
When to use
Use to properly prepare for combining all files in a folder by following best practices.
Instructions
Getting Started
- Create a new query --> From File --> From Folder --> browse for the folder that holds the files
- When you arrive at the preview pane click Edit
Building the FilesList query
- Right click the Extension column --> Transform --> Lowercase
- Click the filter icon at the top of the Extension column --> Text Filters
- Set a text filter “Equal to” the file extension you wish to use
- NOTE: Do not forget the period and make sure you type it in lower case!
- Rename the query to FilesList
- Finalize the query by going to Home --> Close & Load To… --> Only Create Connection
Hints & tips
- If there is a chance that someone will edit the files in the target folder while you are refreshing your solution, you should add a filter to the Name column for “Does Not Begin with” ~ (the tilde character) to ignore temp files
- If you want to target a subfolder, filter the Folder Path column to get to the correct subfolder
- When targeting Excel files, you may want to filter to extensions that “Begin with” .xls as this will pick up all valid Excel files
- 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.