All language subtitles for 029 PRO TIP Appending Files from a Folder_en

Instructor: All right, so we just walked through

how to stack some tables together

using the Query Editor append tools

and we took these three individual sales data files

from 2022, '21, and '20 and combined them

into a new appended data set, right,

where we stacked all three of them on top of each other.

And that worked pretty well.

But remember the downside,

this new table we created is now dependent

on three individual component tables.

So we can't delete any of these individual tables

or if we need to add in new years worth of data,

like, we'll have to reconfigure the append

to make that work.

So, again, it's just, it works decently

but it doesn't scale very well.

So what we're gonna do here

is we're gonna actually delete these four sales queries

and then we're gonna use Power BI

and connect to a folder instead, right?

So first things first, we can delete this append query

and then once that's removed, we can actually start removing

our individual sales data files, right?

Because now, they're not referencing any other queries.

And delete 2021.

And then our last file here for 2022, we'll delete that.

All right, and we're good to go here.

So now that we've done that,

we're left with our six kind of main lookup tables

that we're gonna be using to build out our data model.

And from here, what we want to do

is we actually want to go in

and connect to a new source, right?

That's gonna be that folder.

And I quickly want to show you

what's contained within that folder.

So I'm gonna navigate to that folder on my desktop.

All right, so this is the folder

where we've got all of the AdventureWorks data

and I have this sales data folder here.

I double click into this,

you'll see that we've got our three sales tables

for 2022, '21, and '20 within here.

And this is what we're gonna be connecting to

from the Power BI folder data source.

So we'll jump back to the Query Editor

and we'll come up here to a new source,

and I'm gonna click more for more options.

All right, and from the Get Data window,

you actually see we've got our folder option right here.

We can click connect

and the Query Editor prompts us with the folder path.

Click browse to browse your file structure.

Right, desktop, Adventure Works Raw Data,

and then our sales data, right?

So this is the file path that we want.

Click okay.

And again, this is gonna follow that same process

where it's gonna bring up a data preview window.

We'll be able to check out what that data looks like.

Now, what's interesting here is this preview window

looks a little bit different, right?

It's got this content column that says binary.

It's got file names, and extensions,

and date accessed, and modified.

It's got the folder path string.

So rather than actually previewing the data,

the actual rows and the columns,

what we're actually previewing are the files that exist

within the folder path, right?

So it's a little bit different here

but a similar process, right?

And we've got two options as well.

We can combine and transform

or we can just transform the data.

Either one of these options

is gonna kind of put us into the same spot,

but I'm gonna click transform data

so we can walk through each of these steps.

So here we go, we pressed edit,

we're back in our Query Editor,

and we see that similar kind of view

that we got in preview, right?

We got content and the name,

our file extension, date modified,

created our folder path, and all that kind of stuff.

So again, we're still not looking at data points here.

We're still looking at the attributes of the files

that exist within that folder that we've linked to.

The key here is the first column.

And this column 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.

It's the same idea here.

So you can see when I hover, it says, "Combine Files."

And when we press this,

it runs through a different append process.

So once Power BI finishes that behind the scenes process,

we're brought to this new window

with this combine files header.

And this looks like the data preview that we were seeing

when we brought in our individual sales data tables, right?

Our order and stock dates, order number,

product key, customer key, you know,

order line items, order quantity, right?

So this all looks very familiar.

The other thing that's interesting here too

is if you click on the sample file,

the default is the first file

that you've connected to, right?

So 2020.

But you can actually click through and view previews

based on the other data tables

that are contained within that folder, right?

So this is just another great way to kind of go in

and check to make sure that column structures

and all of that stuff is the exact same.

All right, so everything looks good here,

kind of checks out.

We've got 2021 for our order date.

From here, we're gonna click okay.

All right, we've got a whole bunch

of different applied steps here

that were automatically generated.

If we check out our order date column here, we can load more

and we can actually see, so we've got 2020, 2021, 2022,

all the way through June, right?

So everything looks good there.

Our column structure is all the exact same

with the exception of this new source name column.

And what this column is doing is it just tells us

which file from within the folder that the data came from.

So it's potentially a helpful attribute

if you do want to keep it.

In this case, we really don't need it

so let's go ahead and we can remove this column

from our sales data table.

And there you have it.

We have our appended table

containing all of our sales data in one place.

And as you can probably see here,

it gave us an automatic table name

that's based on the folder, right?

So our folder name was Sales Data, right?

So we could update this if we wanted to.

Honestly, I think that this is a great name for it

so we're gonna leave it as is.

One last thing to note here,

over on the left hand side of the screen

where the queries are,

is we've got a whole bunch of other detail

and information that was automatically generated

by the Query Editor.

And basically, what this is

is this is all of the behind the scenes stuff

that Power BI needed to do in order to append

those three files together from that folder.

So typically, what I'll do here

is I'll just collapse these queries, right?

And just kind of hide them.

And that way, I can kind of stay focused

on my other queries that I have here.

We are ending up with that same end result

as appending those three individuals tables together,

except now, we've got one nice clean table

without all of those extra tables and dependencies.

And even more importantly,

if we ever needed to pull in additional years of sales data,

we could simply drop those CSV files right into that folder,

press refresh right here inside the Query Editor

and all of that data would be automatically pulled in.

So that's just about do it here for this demo

on connecting to data that's located in a folder.

Let's come up here, we'll click close and apply.

And Power BI is working on updating all of those changes

that we made within the Query Editor.

All right, so once this wraps up,

we should see that sales data 2022 table disappear

and just be updated with the sales data table.

All right, so it looks like we're good to go here.

And that's gonna wrap up your pro tip

on connecting to data that's located in a folder.