All language subtitles for 13. Appending Queries in Power BI Desktop

All right.

Next up we've got pending queries and you'll find that a pen queries tool right under merge in the Home

tab of the query editor.

When you click that button you'll see a really simple dialog box like this.

Or you can choose to append either two tables or three or more.

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 they share the exact

same column structure and data types.

So keep that in mind.

Same structure same data types.

So in this case that we're looking at here where appending two tables together there two different adventure

work sales tables one from 2015 one from 2016 and it's a valid pen since they both share identical table

structures.

So just like we talked about merging makes tables wider by adding more columns pending makes tables

taller by adding more rows.

Now protip here than I want to share with you which will actually dive into with the demo in the next

lecture is to use the folder option from the get Data menu.

And what that lets you do is actually Point Power be-I to a folder containing as many files as each

choose which allows you to append all of them at once assuming they share that same structure.

And the beauty of it is that over time if you add new files or remove files simply refreshing that query

that connection will automatically append any new data that's shown up within that folder.

So it's a really powerful option that will dive into in the next video.

But for now let's cover the basic penned option and shift gears and the power be so we can take a look.

OK so I'm here in the query editor.

I'm inside of my A.W. sales 20:17 table and because I've got four different tables here each with a

different column structure.

There's nothing that I can really use to append with what I've got on hand.

So I know that I'm going to need to grab some new data can do that right from the editor by clicking

news source text cxxviii and I want to grab my other two adventure work sales files.

So grab 20:16 first see the preview and can press OK to load straight into the editor.

And why don't we update the table name.

While we're thinking of it A.W. sales 2016 and we'll repeat that process.

New Source cxxviii for the 2015 file or so came from the preview

LEIGH SALES 2015.

OK.

So now you've got three separate A.W. sales files all of which are consistent in terms of the columns.

There's 2015 2016 with one exception in 2017.

Which is that new quantity type column that we added in when we practice that conditional column tool.

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 that conditional column to the full appended version.

In this case we're not going to be using this column for anything.

So let's go ahead and just remove it.

There we go.

And now we've got a perfectly consistent table structure across all three.

So now with one of them selected doesn't matter which one you start with 20:17 in my home tab.

Let's go to append queries and this time instead of the first option.

Got that same choice that we had with merge where you can either append within the existing table or

as a brand new table.

This time let's try the new table.

So when I click that button this time we have more than two tables to pen.

We have three or more and it's automatically populated the first one that I've selected.

So the only other two that I need are sales 2016 and sales 2015.

So we can go ahead and press OK.

And it creates a new table.

It names it.

Append one by default.

Let's go ahead and change that to something like A.W. underscore sales underscore 2015 through 2017.

Just to be clear.

And now it can look through column structures look exactly as we'd expect same column structure as each

component table.

And now when we sort these order dates let's sort them ascending and we see that the table now starts

on January 1st 2015.

And if we expand and load more see the full list you can actually scroll all the way down and see that

it takes us all the way to the end of June 2017.

With 2016 dates in between.

So you've got dates from all three of those sales tables.

So that's great.

We've successfully appended the sales data from our three individual tables into one dependent version.

But one downside of this approach is that now that I've got the table that I want with all the data

in one place let's say I want to get rid of these older individual tables just because they're taking

up space by right click and try to delete the 20:15 query.

It won't let me because it's being referenced by another query that appended 25 through 2017 table that

we just created.

So the problem here is that our new table or 2015 through 2017 table is dependent on the other three.

So we have to kind of keep all four of them here which is OK.

There's nothing wrong with that it's not going to create any big problems.

It just feels a little bit messy.

So one way around that which I very much recommend 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 going to show you exactly how to do that.