All language subtitles for 028 Appending Queries_en

Instructor: Up next we've got appending queries,

and you'll find the appending queries tool

right under Merge in the Home tab of the query editor.

When you click that button,

you'll see this really simple dialogue box

where you can choose to append either two tables

or three or more together.

Now, both merging and appending are similar

in that they're adding information to an existing table,

but they're different in that merging is adding columns

and appending is stacking rows.

So basically, appending queries allows you

to combine or stack two tables,

assuming that they share the exact same column structure

and data types.

So that's super important to keep in mind.

Same structure and same data types.

So in this case that we're looking at here,

we're appending two tables together.

There are two different AdventureWorks sales tables,

one from 2020 and one from 2021,

and it's a valid append

since they both share identical table structures.

So just like we've talked about merging

makes tables wider by adding more columns,

appending makes tables taller by adding more rows.

So a pro tip here that I wanna share with you

and we'll actually dive into this

with the demo in the next lecture

is to use the folder option from the Get Data menu.

And what this lets you do is actually point Power BI

to a folder containing as many files as you choose,

which allows you to append all of them at once,

assuming that they share that same structure.

And the beauty of it is that over time

if you add new files or remove files,

simply refreshing the query,

refreshing that connection will automatically append

any new data that's shown up within the folder.

So it's a really powerful option

that we'll dive into within the next video.

But for now, let's cover the basic append options

and we'll shift gears into Power bi so we can take a look.

Alright, so we're inside the Query Editor

and I'm in my Sales Data 2022 table.

And we've got a bunch of data sources in here.

But the problem here is that none of these data sources

share the exact same structure or data type.

So what we need to do is we actually need to go

and get some new data.

Again, we're gonna do this

right from our New Source tools,

and we're gonna go and we're actually gonna bring in

our other two sales data sets.

So let's connect to 2021.

All right, preview looks good.

All right, I'm gonna quickly update my table name again,

and then our column headers,

data types, everything looks good here.

All right, let's go back. We'll get our 2020 data set.

And we'll add this into the query editor.

And following our very similar repeatable process,

we've got our data preview.

Again, everything looks good here.

Same steps.

We'll update our table name

and then I'll just confirm again that our data types,

column headers, everything looks good.

All right, so now we've got

these three separate sales data files, right?

For 2020, 2021, and 2022.

And they are all consistent in terms

of the number of columns,

except for our 2022 data set, right?

This is where we had added in our quantity type column

when we were practicing our conditional column tools.

So one option would be to go ahead

and continue to append these together.

You'll see some null values

for the quantity types in the other two tables.

And you could pretty easily redefine the conditional column

to the full append version.

In this case, we're just gonna go ahead and remove it.

So we come over to this column,

we can actually delete our last applied steps.

And now we're back to the same exact structure

across all three of these tables.

So with one of them selected,

it doesn't really matter which one,

I've got 2022 selected here,

from the Home tab, we're gonna go to the Append Queries.

Again, right below merge.

And again, we've got those same options

that we saw in the Merge Queries.

We can append queries or append queries as new.

And for this example, I wanna append queries as new.

I wanna create a new table that's based

on these other three queries.

All right, so we've got our dialogue that pops up

and we actually have two or more tables,

so we're gonna collect three or more tables.

You can see here that we already are showing

our sales data 2022 as part of the append,

'cause that's what's selected,

and then we have all of our other available tables here.

And if we scroll down, we can hold down Shift

and click 2021 and 2020, and then click Add,

and now we've got all three of these tables

that we are gonna append together.

Click OK.

So here we go, we've created this new table.

Its name is just Appended1 by default,

so let's update that to something

a little bit more readable.

So we'll call this Sales Data 2020 through 2022.

Again, just to be clear.

And now we can look through the column structure here.

We can see that our columns are exactly the same.

We select our order date drop-down and view more.

We've got all of our 2020.

Scroll all the way down here.

You can see all the way down to June 30th, 2022.

All right, and then probably in the middle here,

we've got all of our 2021 data.

So everything looks good there.

We've successfully appended all of our sales data together.

So everything might seem great,

but one downside to this approach

is now that I've got this table with all of the data in it,

let's say I wanna get rid of these individual tables here,

if I right-click and select Delete,

I get a message from the query editor saying like,

"Hey, we can't delete this table,

because it's being referenced by another query."

And Power BI won't let me actually delete this

because it needs this table in order to populate

this table that's appended.

So the problem here is that our new table,

our Sales Data 2020 through 2022,

is dependent on three other tables.

So we kinda have to keep all four of 'em here,

which it's okay, but honestly it's not the greatest.

We've got duplicate redundant queries in the editor,

and while it's probably not really

gonna create any big problems,

it's just messy and not great form.

So one way around that, which I recommend very much,

is to move these files into a folder

and then connect to the folder itself.

So stay tuned, because in the next video,

I'm gonna walk you through exactly how to do that.