Append All Files in a Folder - Application

Quick reference

• 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.