‫All right, so I just showed you how to stack some tables together using the query editing of pen tools.
‫We took these three individual adventure work sales files from 2015, 16 and 17, and we appended them
‫as a new table called A.W. Sales 2015 through 2017, which included all of the data across all three
‫files.
‫And that worked pretty well.
‫But remember the downside.
‫This new table that we created is now dependent on the three individual component tables, so we can't
‫delete any of these individual tables.
‫We have to keep this whole set of four.
‫So let's actually scrap these four sales files and pull them into power by using the folder approach
‫instead.
‫So I can delete the appended file first.
‫And press och.
‫And once that one's gone, now I can delete any of these individual ones because no relationship between
‫them exists and there are no more dependencies than we need to worry about.
‫So 2017 delete.
‫There you go.
‫So all of our sales data is gone.
‫We're left with just the product.
‫Look up the customer, look up in the calendar, look up.
‫And what I'm actually going to do here is pull up the folder on my desktop containing these individual
‫CSV files and create a new folder there.
‫So go ahead and pull up your folder as well.
‫Okay.
‫So here's where I've got all of my adventure works CSV files, including the three individual sales
‫files.
‫What I'm going to do is actually right click and create a new folder right here within this same adventure
‫works folder.
‫And I'm going to name it something like RW Underscore sales, you can name it whatever you want.
‫And then once I've created that empty folder, I'm going to select each of my sales tables 20, 15,
‫16 and 17.
‫I'm going to drag them all into that sales folder.
‫Now, when you click in, you can see that it contains all three of those files.
‫And once I've done that, I can go ahead and close that file window, return back to my query editor,
‫and I'm going to go ahead and grab a connection to a new source.
‫And this time I need to go down into the more options.
‫And right here on the right, you'll see the option to connect to a folder.
‫So let's go ahead and connect.
‫And the first thing that PowerBar is going to ask for is the folder path.
‫So let's go ahead and browse and navigate to those course files.
‫Powered by course files.
‫Adventure works, few sales.
‫So that's the folder that it just created that contains those three individual sales files.
‫And I can go ahead and press OC and OC once more.
‫Now the preview window looks a little bit different.
‫It has this column that says content binary.
‫It's got file names and extensions.
‫So rather than actually previewing the data, the actual rows and columns, it's previewing the files
‫that exist within this folder path.
‫Now we have two options here.
‫We can either combine and edit on the spot, or we can edit and actually walk through each of the steps
‫of the combination.
‫So I'm actually going to choose the edit option because I want to show you exactly what this looks like
‫bit by bit.
‫So there we go and I press edit.
‫It lands me here in the query editor and I see that similar kind of view that I got in the preview.
‫Right.
‫I'm not looking at actual data points.
‫I'm looking at attributes about the files that exist in the folder that I've linked to.
‫So the key here is to look at this first column.
‫This is kind of similar to the column that was created when we merged tables where we had to take one
‫extra step to actually split out the columns from the table itself.
‫Same idea here.
‫We have to take one extra step to break out the actual content within these CSV files located in the
‫folder.
‫And to do that, we'll just press this combine files button right here in the content column header.
‫And when we do that, it runs through the append process and opens up a new preview window with a combined
‫file's header.
‫And this shows us an actual data preview from each of our files.
‫We can select the first, the second or the third here.
‫And if our preview looks good, if we know we've got the same column structure data types, which we
‫now do, then we can go ahead and press.
‫Okay.
‫And after it runs through some processing, you'll see that the data lands right here in the preview
‫window and contains data from all three of our files.
‫So same thing we can filter on that order date show more.
‫We're starting in January 2015 and our dates extend all the way through 2016 through the end of June
‫2017.
‫So we've got all of our data in here.
‫Got all of our familiar columns out to the order quantity column.
‫We do have one additional column here, which is the source name, and that just tells us which file
‫within the folder that data came from.
‫So kind of a helpful attribute if you do want to keep it.
‫In this case, we don't need it.
‫So let's go ahead and remove that source name column.
‫And there you have it.
‫We have our appended table containing all of our sales data in one place.
‫And as you can see, it automatically gave us a table name based on the way we named our folder, which
‫in this case was a w underscore sales.
‫We can either leave it just like that or add the years to that 2015 through 2017.
‫In this case, I think this is the final version of the sales table that I'm going to use.
‫So in this case, a sales it's nice, it's clean, it's concise.
‫That'll work just fine.
‫So last thing to note, over here on the left side of the screen, you'll see that a sales table showed
‫up right beneath our calendar.
‫Look up.
‫You'll also see some automatically generated query information above that.
‫And this was all automatically written, automatically generated in order to make all that magic happen
‫by penning the files within the folder.
‫So we don't even have to touch this.
‫We don't even have to think about this.
‫I usually will just kind of collapse it.
‫And then in this other queries folder, this is where all of our kind of primary tables will continue
‫to live.
‫So again, same end result as loading up three individual tables and using the append tools from the
‫home menu.
‫Except now we have one nice clean table without those extra tables and dependencies.
‫And even more importantly, if we ever need to pull in additional years of sales data, we could simply
‫drop those CSV folders right into that sales folder and press refresh right here inside the query editor.
‫And all of that data would automatically be pulled in.
‫So that should just about do it.
‫Last step is to close and apply.
‫And then we'll go ahead and load that new sales data to our file.
‫And let's take a look at relationships.
‫There it is, sales.
‫So with that, there's a pro tip connecting power BI to a folder of files.