Locked lesson.
About this lesson
This lesson will teach you how to unleash the magic of appending all files in a folder, and show you how effective it is.
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 - Application.xlsx21.4 KB Append all files in a folder - Application - Completed.xlsx
1.6 MB
Quick reference
Append All Files in a Folder - Application
A demonstration of the process for appending all files in a folder into a single query.
When to use
Use to combine binaries after setting up a FilesList query.
Instructions
Getting Started
- If you have not already created a FilesList query, DO THAT FIRST!
- Expose the Queries pane --> right click the FilesList query --> Reference
Master Query – Initial modifications
- Rename the Master query immediately to a more logical name (still referred to as Master here)
- Click the Combine Binaries button and click OK
- Wait for the four queries to be created on the left side of the window
- Delete the Changed Type step that was added to the Master query
Transform Sample modifications
- Select the Transform Sample in the queries pane on the left side of the window
- Perform any required transformations to the Transform Sample query
Master Query – Final modifications
- Select the Master query
- Modify the Removed Other Columns1 step to keep any desired file properties
- Add any additional transformation steps required to the Master query
- Set the Data Types
- Finalize the query by loading it to the desired location
Hints & tips
- Using the FilesList query avoids the file path being hard coded into the solution twice
- Using the FilesList query also makes it much easier to sort and change the file used by the Transform Sample
- To change the file used by the sample transform, re-sort the FilesList query, go to Home --> Refresh Preview. The new file will now be the one used in the Transform Sample
- 00:05 We're now ready to actually append all the files in the folder.
- 00:08 But if you've opened up the sample template here, the challenge you're gonna
- 00:12 need to do is you're going to need to get to the point where you can actually
- 00:14 edit the queries to update the source file path.
- 00:18 To do that, we need to find the queries pane on the right-hand side.
- 00:23 In Excel 2016, that's gonna be one the data tab.
- 00:26 It's either gonna be under a button called Queries and Connections, or Show Queries.
- 00:31 In Excel 2010 or 2013, it'll be on the Power Query tab on a button called
- 00:35 Show Pane, about two thirds of the way across.
- 00:38 And in Power BI desktop, you just go to the home tab and click Edit Queries.
- 00:43 In Excel,
- 00:43 this will bring up the queries list, where we can see we have the files list query.
- 00:48 We're then gonna right click, and choose Edit.
- 00:52 And this allows us to go into the query editor so
- 00:54 that we can actually make some changes.
- 00:57 Naturally, the first one you're gonna wanna do is grab the source,
- 01:00 click the year icon, and
- 01:02 browse to make sure that you're pointed at the beginning folder in append all files.
- 01:06 Now at this point, what I'd like to is I'd like to actually call
- 01:12 your attention to the queries pane on the left-hand side, we're gonna expand that.
- 01:16 And we're gonna create a new query by referencing the files list query.
- 01:21 So we're gonna say right-click and I'm gonna say reference.
- 01:26 Now, even though it doesn't look like it right now,
- 01:28 this query is gonna be our transactions query.
- 01:31 So, I'm gonna go and call it transactions.
- 01:34 Notice in the formula bar, it's pointing to equals files list.
- 01:39 So, whatever happens in this query will be passed directly to
- 01:43 the transactions query where we need it.
- 01:46 And we're now at this magic point where we can go and combine our files.
- 01:53 To do that,
- 01:54 we're gonna come up to this button here that has the double down arrow.
- 01:57 Which is the combine files button, and we're gonna click it.
- 02:01 Now before I do this, I wanna be really clear on something.
- 02:03 It's very important that you rename this query before you do this.
- 02:07 It's very important that you've referenced the file's list query to get a new query
- 02:11 that you've named.
- 02:12 That is what actually allows the break the cell so
- 02:14 then we only get one hard coded file path.
- 02:17 Renaming it, you're gonna see in a second, why.
- 02:20 We're gonna say combine files, and it will pop up a nice little dialogue that says,
- 02:25 hey, I looked through your folder and I found a sample file.
- 02:29 Would you like to use the first file,
- 02:32 which is pretty much the only option you're gonna get.
- 02:35 When you actually look at it, the rest of the data inside here, it gives you a nice
- 02:38 preview and it looks just like you're importing a regular CSV file.
- 02:42 So I'm gonna now go and say, okay, and at this point stuff happens.
- 02:48 There's gonna be a very big explosion of queries on the left hand side and
- 02:52 there it is.
- 02:53 Now, you'll notice, let me open this up, that every one of these guys here,
- 02:59 transform sample file from transactions, transform file from transaction.
- 03:03 Had you forgot to name the transactions query before you did this,
- 03:07 it would have the transform file from files list two,
- 03:11 which is really kind of gross and hard to actually deal with.
- 03:16 Keep in mind that we are still in the transactions query.
- 03:19 And you will notice that we have a source, which looks like our original list.
- 03:24 We have an invoke custom functions step, which was added that adds this table.
- 03:30 It then removed other columns one,
- 03:32 leaving just this particular column with the table.
- 03:36 Expanded that into individual columns, and
- 03:38 then power query attempted to set up a change type step.
- 03:43 Now, I'm gonna leave this here for right now, just to demo how this works.
- 03:47 But I would suggest normally that the first thing you should do is delete this
- 03:50 step to save yourself the problem you're gonna see coming from me
- 03:52 a little bit later.
- 03:54 Which queries do we play with?
- 03:56 Well, lets take a little look, sample file, parameter one.
- 04:00 Just says sample file,
- 04:01 this looks really unfamiliar, you got no idea what to do with this.
- 04:04 So don't do anything with it, you can just break it.
- 04:08 Sample file, because we separate the file system transactions query
- 04:13 you'll never need to deal with this query.
- 04:15 It looks funny, it looks weird, doesn't look like a table, leave it alone, okay?
- 04:19 Otherwise you're gonna break something.
- 04:22 The transform file from transactions automatically inherits all of its steps
- 04:26 from the transform sample file from transactions.
- 04:29 Again, it looks different, doesn't look like a table, so leave it alone.
- 04:33 The transform sample file, this one looks like a table.
- 04:37 And in this one, we can do the things that we would normally do.
- 04:40 So, I could go in and say hey, you know what, I wanna change this,
- 04:43 this needs to be hour.
- 04:45 And I want to change my gross dollars to be gross sales and
- 04:51 I want to filter out anything that begins with,
- 04:57 actually I want does not begin with an asterisk.
- 05:02 So shift eight.
- 05:04 Maybe I wanna make my replacement on the casing on this.
- 05:08 So I'm going to go and say transform and change this to capitalize each word.
- 05:13 And maybe I want to go and do another replace values on this and
- 05:17 maybe I wanna replace, for example, the slash with a space and
- 05:25 space in order to fix the soups and salads and other things.
- 05:31 The other things I may wanna do is I may wanna set my decimal places in here.
- 05:34 So a whole number for
- 05:35 this guy here for hour, my unit sold is gonna be a whole number.
- 05:40 And my average price is gonna be a decimal number.
- 05:43 My growth sales a currency.
- 05:45 At this point I've pretty much done all the things we did the first time we
- 05:48 imported this particular file.
- 05:51 I'm now gonna go back to the transactions query and
- 05:54 you're gonna notice that things are broken.
- 05:56 Because it says where's POS chidar.
- 05:59 Well, of course, I renamed that column.
- 06:01 So let's delete this step.
- 06:04 And look at that, everything comes back nicely,
- 06:07 except that it didn't bring my data types across.
- 06:10 So I've got to reset those really quick.
- 06:12 Whole number, this one here is also gonna be a whole number.
- 06:17 My average price is gonna be decimal, my gross sales a currency, and
- 06:21 the last three columns we'll select and say Change Type to Text.
- 06:29 What if I also wanted the file name here?
- 06:31 I would come back to remove other columns one, click the gear icon,
- 06:37 and say hey, I want this column name.
- 06:41 This is the file name.
- 06:44 So now I can go back down to Change Type and
- 06:46 you'll see that I also have my file name associated here, so this is good.
- 06:52 We've done a sample transformation in here, or rather in the transform sample,
- 06:56 that applies to each individual file.
- 06:58 And in the master query we've added something else.
- 07:01 Let's go and hit Close and Load.
- 07:04 We'll let it load all these queries and it treats them properly.
- 07:07 And then I'm gonna go and very quickly say,
- 07:10 Insert a Pivot table on a new worksheet.
- 07:14 I want to show you the files that we actually have here.
- 07:16 So here's the file name and
- 07:18 here's the total gross sales that were actually in here.
- 07:22 Because now, things are about to get amazing.
- 07:26 The folder we targeted to begin with only had a single file in it.
- 07:31 However, at the root of that append files and folder, there's also a more folder and
- 07:36 that one has a few more, so, watch this now.
- 07:40 We'll just right click and copy this and we'll come and
- 07:43 paste this right inside the begin folder.
- 07:46 Notice it's a nested folder and
- 07:49 now what I'm gonna do is, I'm gonna come back over here and we're gonna go to Data.
- 07:54 And we're going to hit Refresh All and at this point Power Query through and
- 07:58 it's gonna bring in a whole pile of new transactions except that
- 08:02 our pivot table did not update.
- 08:05 Something you do need to be aware of sometimes we have to do this twice.
- 08:10 And look at that, I've just pulled in all of the same data, run it through the same
- 08:14 steps, and boom, I've got a pivot table that has all these files.
- 08:17 And naturally, I could totally make this bigger if I wanted to and
- 08:21 break this out with pivot table to make it more complex, all that data is there.
- 08:25 The key thing you need to remember here, the Power Query chain refreshes first,
- 08:29 the Pivot Tables refresh second, unfortunately.
- 08:32 However, we've just managed to go through and
- 08:35 apply all of the same steps that we made on our individual sample transform.
- 08:39 Applied it to all of the files, and append them all together in the master transform
- 08:43 into a big table to feed our Pivot Tables.
- 08:46 How cool is that?
Lesson notes are only available for subscribers.