2.11 Append queries - Video Tutorials & Practice Problems

<v Teacher>Sometimes when getting data from sources,</v> data can be spread across many different source systems. And we ultimately want to bring all that data together and append it into one table. What we're going to talk about in this sub lesson is how we can take multiple different queries, which may be coming from different sources, and get them appended into one data set. So, for example on the screen here, we have two queries, query one and query two. And we can see that each query has four columns. But if we really take a good look at the query names, there's actually differences between the column names within those queries. So we can see in query one, we've got a column called product number with no space between the word product and number and in query two, we've got a column called product number, but there's a space between product and number. So that's important because that will basically indicate that those are actually two different calls. So to the human eye, we may look at those and say, Oh, those should line up through the exact same column. But when it comes to how to the appending works, they are seen as two different columns, because their names are different. We take a look at the name column and the product name column. Once again, two different column names that are going to be appended into two separate columns. Will notice the finish good flags exists only in query one, but not query two, the color exists in both and it has the exact same name, so those two are gonna line up nicely. And supplier only comes from query two, and there's no value for a column called supplier in query number one. So what happens is if we want to append these two queries together, like so, the final result will look something like this. Well, we'll end up with this third query, which has a column called product number, which comes from query number one, a column called name, which it's values will come from column number one, and the values down here are blank because there are no values in query number two for a column called name or product number with no space. We'll see the finish good slide comes from query number one only. Color actually comes from both. So we can see that actually appends together properly. And then the columns that didn't line up right where the product number with the space shows up down here. The product name of the space is here. And the supplier, which only exists in query two shows up down here. So if we wanted these to line up properly, one of the first things to do before you do your appending, is make sure you go through and standardize the names between the queries you want to append, so when it lines them up, it lines them up properly, because ultimately, what we want to see happen if the name's right, is these values would actually be sitting over here like so. And then things would line up nicely. That's not the case. So they were over here. And that just means that we need to go ahead and clean our queries up. So let's go and show you an example using the real data set. So I'm going to go back over to get data and go into my power query editor. So I'll click on that. What I'm going to do here first is I'm going to click on my products query because I want to append products and product finished goods together. And I want to do this as a brand new query much like I illustrated back on the previous slide. So under the Home tab, with combined group under the combined group here, I can click on append queries, I'm going to say append queries as new, so I can create a brand new query which is an appended version of those two queries. The primary table is products because that's what I was highlighted on when I kick this dialog off for this process. And then the next I can say the tables to append to the primary table are, my product finished goods. So I'm going to append these two queries together. And we're going to get a brand new query called append one showing up here. And this one I would want to rename, which we'll do momentarily here, and we can see now that our columns lined up. So I had the product number with a space in both so within the products query, and the product finished goods. So these lined up nicely. So these would have all shown up in the product number column, I made sure the same standardization was in place for product name. The finished good flags, once again was standardized across both. And if I go all the way over to the end here, we'll see that all of the columns actually lined up properly aside from the fact that the first character is one, because if you recall back in a previous sub lesson, we took the first two characters in the product query and pulled them out. So we're going to see that the first characters are showing up in some and they're null in the other ones and when they're null these are the ones that came from the query for product finished goods, because product finished goods did not have a column called first character, so had no idea what to do, it just put null into that column. So now what we have is this brand new query called append one, we'd want to go ahead and rename this. And we could go ahead and just call this product list or something like that. And this is essentially the list of the complete product. So bringing the finished good products and the products query which we had pulled in a little bit earlier using the folder connection type. And now we have a complete set of product information. So let's go ahead and enter. Let that query take. And that is it. That brings us to the end of this sub lesson.