All language subtitles for 14. PRO TIP Appending Files from a Folder

All right.

So I just showed you how to stack some tables together using the query editing pen tools.

We took these three individual adventure work sales files from 20 15 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 it's actually these for sales files and pull them into power by using the folder approach instead.

So I can delete the appended file first press OK and once that one gone.

Now I can delete any of these individual ones because no relationship between them exists and there

are no more dependencies.

And we need to worry about.

So 2017.

There you go.

So all of our sales data is gone we're left with just the product.

Look up the customer look up and calendar look up and what I'm actually going to do here is pull up

the folder on my desktop containing these individual CXVII files and create a new folder there.

So go ahead and pull up your folder as well.

OK.

So here's where I've got all of my adventure works.

Cxxvi files including the three individual sales files.

When 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 A.W. underscore sales can name it whatever you

want.

And once I 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 A.W. sales folder now and you click and 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 when no 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 it's going connect and the first thing that power is going to ask for is the folder path.

So let's go ahead and browse and navigate to those coarse files RBI of course files Adventure Works

A.W. sales.

So that's the folder that it just created that contains those three individual sales files and you can

go ahead and press OK and OK once more 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 previewing the files that exist

within this folder path.

Now we have two options here.

We can either combine and it 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 at it.

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 the CXXVI files

located in the folder and to do that we'll just press this combined files button right here in a content

column header

and when we do that it runs through the append process and opens up a new preview window with a combined

files Hetter.

And this shows us an actual data preview from each of our files.

We can select the first second or the third here and if our preview looks good if we know we've got

the same column structure data types which you know we do then we can go ahead and press OK.

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

Do you 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.

You 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.W. sales it's nice it's clean it's concise.

That will work just fine.

So last thing to note over here on the left side of the screen you'll see that A.W. 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 appending 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 an additional years of sales data we could simply drop those CXXVI folders right

into that A.W. 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 I'll go ahead and load that new A.W. sales data to our file

and take a look at relationships.

There it is A.W. sales.

So with that there's a protip connecting power be-I to a folder of files.