It's quite common to have multiple data sets of the same type that are split apart for size management reasons, such as having January sales stored separately from March sales or June sales. In this lesson, I'm going to introduce you to the append command within Power Query. With this command, we'll be able to combine multiple datasets together end to end, as if you were stacking them on top of each other. In simple terms, you can think about the append command as if you're copying one dataset and pasting it at the bottom of another data set. While following a very simple rule. You'll be pasting the data so that each column header from one table will match the column table header from the other.

Let's use these two tables and append them together as a demonstration. The left table has a list of fruits and a list of stores. While our right table has a list of fruits and a list of store name. Imagine pending the right table to the left table, that means that we'll be copying and pasting each column from the right to the appropriate column on the left. Appropriate column is defined pretty strictly the names must match exactly. So how do you think this scenario will play out?

Since this is a small data set, let's perform the upended Excel the same way the Power Query append would, each table has a column called fruits, so it will match up pretty perfectly meaning I can copy the fruits on the right immediately into the fruits on the left. The other column does not match stores is not the same as store name. How does Power Query handle this mismatch? It's actually going to do two different steps. The first step is extending the stores values to include a blank entry for each of the fruits. The second step is going to be to add a new column called store name to the right of stores.

And in this column, that's where the store name data from our second table will be sword. This action is performed for each column in each table, matching column Get stacked together, while mismatched columns will be placed in their own fields. This creates a single table with all of our information even if it's not quite oriented the way we would like it to be. Having seen this basic concept in action within Excel, let's take it over to our query editor and perform an append to there. We have the same tables we use just a moment ago, but each has been loaded into a Power Query. I'm going to right click and edit the stores query to serve as a starting point appending queries together as a matter of just a few mouse clicks.

The append queries button can be found on the Home tab, and clicking on it will initiate a wizard consisting of two options, two tables and three or more tables. The general functionality between the two is the same, but the three or more options interface will let you append more than one table together at a time. I'm going to start with the two tables approach. And once at this point, all you need to do is select the table that you want to attend from the drop down. Once you've done this pressing OK we'll Have the data get appended to the end. For this example, we can see how a new store name column has been added.

And all of our information is appended to the very end. So the two table options pretty quick and easy. How much more challenging is the three or more option. Let's go ahead and delete this step and redo it. This time, I'm going to select the three or more tables option. Instead of having a drop down here, I have two lists.

The left side shows all of the tables that are in my workbook that I can use for this append command. Note that it will label the query you're currently in and included on the right by default. The right side shows the list of tables that you selected to append together. to replicate our prior command, we need to add the Z store names table to the list on the right by selecting the left and then clicking the Add button in the middle. Once both queries are listed, pressing OK will create the same output we had moments ago when using the two table option. quite clearly the three or more table approach is almost As easy as the two table approach to us, let's go and re edit the command and bolt surprise, the editor has condensed our command to a two table append command.

Let's change this back to three or more. I'm going to add another copy of this east or names data from the left to the right. And once I've done this, I'm going to press OK. In our data set, we now show two separate copies of the same data as having been upended once for each of the entries we placed in the append command. One issue with our append command is that we've left two columns disjointed, when we really know that they should be a single column of information. For a scenario like this, we'd like to combine them together.

When one column is blank, use the other column and vice versa. To do this, I can select both columns by holding the Ctrl key and clicking on each one. Once I have them selected, on the transform tab, you can find the merge columns command. This command will bring up a new prompt to help you To figure out how to combine the columns together, whether to separate the data with a separator, and what column name to use, This command will place the first column on the left, then add the separator before adding the second column to the right. So commas could be used to create a name field. In this case, I do not want any separators since I have either a null value or the store name, so I just want whatever value I'll get.

Once you've settled on a separator, you're ready to choose the name of your new column. I'm going to name it store name and press OK. Upon refreshing the data will merge into a single column called store name dot one, which I'll quickly renamed a store name. At this point, we've appended our two queries together and merge the stores and store names column into a single field. append commands can be very useful for stacking datasets together that are in essence, the same data structure with different contents. You could stack older data from a backup with newer data for Long horizon report, where you combine a series of monthly reports together to create an annual report.

In conjunction with the merge columns command, stacking data together has never been faster or easier and Excel. Power Query is ability to connect datasets together does not stop it as simple data append. Our next lesson, we'll look at how easy it is to weave data together through a Power Query merge