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.