Append data sets - Microsoft Excel Tutorial

- [Instructor] A common need is to get data stacked up in one place. Sometimes you have a bunch of data that's come to you in pieces. Now you can start copying and pasting, dragging cells around. That's messy and error prone. Now let's look at this data that's come to us in three pieces. There's the Gladd county data. We've got columns for city, the year the city was founded, the number of high schools and the city hall address. So Port Cloyer was founded in 1837. It doesn't have any high schools and the address is 19 Cloyer Avenue. Okay, Raddlit. Here's its data. Five cities. Oh, and this has a mayor column. The other data set did not. Okay, now let's look at the other data set for the county of Barco. Notice this data set does have a mayor column but it's not in the same place as the previous data set. So this is where you can see if you had to copy, paste this or manually stack this up, there's a lot to deal with because it's not going to paste together perfectly. And this is where I'm going to show you Power Query's append function. Now I'm going to go to data, queries and connections. You can see over on the right that I already have queries for Gladd county and Raddlit county. Now I need the data for Barco county. Put my cursor in the data set. I'm going to right click, get data from table slash range. My table does have headers. Okay. I'm going to call this Barco. That's the name of our query. Slide over. Okay, there's all of the data. Look at Gladd and look at Raddlit. Everything is here. Let's get it stacked up. Append queries, append queries as new. I'm appending as new because I want a separate query when I'm done. If I were to append right now, the other two counties data would be appended onto the Raddlit data. I don't want that. I want a separate query when I'm done. And I do have three or more tables. Available tables, you see all three. Now over on the tables to append section, we have to think about which table we want on top. In this situation, we don't want Gladd on top because Gladd does not have a mayor column. The other two do, so we could have either Raddlit or Barco on top. Let's leave it with Raddlit and then I'm going to highlight Barco, add, and then Gladd, add. Now this is exciting. Here we go. Hit okay. Look at that. The data is all stacked up with Raddlit on top and then Barco, and then Gladd. Let's slide over, look. And when the Barco county data was added, the columns were put in the order that they were in Raddlit. So the yes, so the mayor column is on the end. I'm going to do one thing. I'm going to go back to the first column. In case we need to sort this, filter it, we need to know where these cities belong, which county they're tied to. With that column highlighted, I'm going to go over and right click, fill, down. Wow, Power Query has filled down, filled in those gaps in the columns. Now we can sort, we can filter, and the cities will stay with their respective counties. And one other thing to mention. For columns that you want to match up, they do have to have the same names. We saw that they don't have to be in the same order or even have the same columns, but they do have to have the same names. So the city column, you can't have a column in your source data that says cities. Power Query will see that as a whole separate column. So let's do one thing here. I'm going to highlight the founded column and then sort it ascending. There we go. Now, Port Elkin doesn't have a founded date. Lamphiere was founded in 1877. Scroll down, okay. Everything looks good. Now I'm going to close and load this to the workbook. Close and load. I'll bring this up a bit. There is our data all stacked up. Here is our Barco data. And I'm going to delete this because we only want this as a connection only. We didn't do anything with this data. So right now, it's just in the way. So I'm going to delete it and notice over on the right side, its going to go from five rows loaded to connection only. Delete and delete connection only. Great. One other thing to show you, and then we can get out of here. I want to slide over. Here is some data that was in some hidden rows. Unhide it. Okay, so we've got two more rows of data for the county of Barco. Let's move this into position. Okay, here we go. And this does need to be in order with the mayors next to the city names. Okay. I'm going to hold down the Shift key, grab this, slide it over. Okay, now let go of the Shift key. Okay. Now everything is lined up. I'm going to highlight, bring this into the table. The table absorbed the new data. Go over to append and then go to data and refresh. Ha, the new data has been incorporated. That county columns data, the fill down took care of that. There are no holes in that data and the data is sorted. And we can do this because of what? Because Power Query's append allowed us to stack this data up in one place.