The more we dig into Excel’s Power Query feature, the more we find out just what a powerful tool it really is. For example, Power Query includes tools to ease the process of linking data into Excel from external data sources and applying transformations to make that data more useful. But it also facilitates quick-and-easy data transformations to make our data more usable and useful. And, as you will learn in this tip, you can use Power Query’s Combine Queries feature to quickly merge data from multiple worksheets or workbooks.
Merge And Append Queries In Power Query
The Merge Query feature in Power Query allows us to create a new query from tow existing queries. When merging queries, the source data can be Excel-based, or it can come from any other data source that you can query into Power Query. Further, when merging queries, one of the queries will serve as the source for the “primary” table of data and the other will serve as the source for the “related” table. This environment allows you to easily link the data from the merged queries into a single data set.
On the other hand, the Append Query feature in Power Query facilitates querying one set of data into a data model, followed by another set of data that appends to the bottom of the first set. In effect, append simply adds all the rows from the second table to the bottom of the first table.
Creating Fundamental Queries in Power Query
In this example, we have two workbooks – Actuals.xlsx and Budget.xlsx – and we need to append the data of one query to the data generated by a second query. Certainly, we could complete this task with a manual cut-and-paste operation. However, let’s assume that this is something that we need to do repetitively; therefore, we want to use Power Query to automate the procedure. To do so, begin with the following steps:
- In a blank workbook, from the Data tab of the Ribbon, click Get Data, From File, From Workbook, and navigate to and select the xlsx workbook.
- Click Import.
- As shown in Figure 1, click Load To… and choose Only Create Connection.
- Click OK to complete the process of creating the first query.
With the first query completed, re-perform the steps outlined above for the second workbook, Budget.xlsx. Again, choose the Only Create Connection option in the Import Data dialog box.
Appending Query Results
With both of the “base” queries in place in the workbook, choose Get Data, Combine Queries, and Append from the Data tab of the Ribbon. In the resulting dialog box, indicate that you will be combining two tables. Also indicate that the Actuals table will be the primary table and that the Budget table will be the related table and click OK. Figure 2 illustrates this process.
The Power Query Editor window then opens, providing you with a preview of the data. Figure 3 presents a sample of the results. Notably, in this case not only did Power Query add all the rows from the Budget table to the resulting Data Model, it also added the column Budget to the Data Model. Of course, it did so because that column did not previously exist in the Actuals table.
adding the finishing touches
At this point, we could complete the process by clicking Close & Load in the upper, left corner of the Power Query window. However, let’s add a few steps to make this appended query even more useful. First, click the drop-down arrow in the Month column and uncheck the box next to Null. Of course, this step will filter any rows where the Month field is blank out of the results of the query.
Next, ensure that the Month column is selected; additionally, select the Product column by holding down the CTRL key while clicking on the column header, so that both the Month and the Product columns are selected. With both columns selected, click Group By on the Home tab of the Power Query Editor window. In the Group By dialog box, change the New column name field from Count to Actual, change the Operation from Count Rows to Sum, and set the Column to Sales. Next, click Add aggregation to add another row and set the Column name for the newly-added row to Budget. Also set the Operation to Sum and the Column to Budget. When done, the Group By dialog box should look like that pictured in Figure 4. Click OK to complete the process.
Having completed the steps outlined above, the data is now grouped based on the combination of Month and Product, as shown in Figure 5.
returning the data to excel
In the upper, left corner of the Power Query window, click the drop-down arrow next to Close & Load and select the Close & Load To… option. Choose to load the data into Excel as a Table, as shown in Figure 6.
Figure 7 displays a subset of the results of the appended query. Notably, if either of the two underlying tables change, all that needs to be done to update the data pictured in Figure 7 is to right-click on the pictured table and choose the Refresh option or right-click on the append query in the task pane near the right side of the window and choose Refresh. Either of these actions will update the results of the append query.
Summary
As mentioned earlier, Power Query provides a seemingly unending set of tools and features to help us automate task. Clearly, the ability to combine queries using the Append process described in this tip is one of the more powerful options we have available to us. With this feature, we can automate the otherwise mundane process of combining data from multiple data sets, particularly if this is a task that will be performed repetitively. So, the next time you need to join two (or more) data sets into one, take a close look at how Power Query can help you with that process.
You can learn more from Microsoft about Append Queries by clicking here. Additionally, you can learn more from Microsoft about Merge Queries by clicking here.
K2 Enterprises is pleased to offer the following learning options related to Excel’s Power Query feature:
Click here to learn how you can use Power Query to “unpivot” data that has been previously summarized.
