Creating the needed base queries

The Append feature works on only existing queries. That is to say, no matter what kind of data sources you have, you need to import them into Power Query before you can append them together. In this case, it means importing all the region tables into queries.

Follow these steps to import the needed base queries:

1. Go to the North Data worksheet, place the cursor anywhere inside the table, and then choose Data ⇒ From Table.

   The Query Editor activates, showing you the contents of the table you just imported.

   To finalize the creation of the query, you need to close and load the query. Now, because you’re creating this query simply for the purpose of appending it to other queries, you don’t need to close and load to the workbook. You can choose instead to close and load the data as connection-only.

2. On the Home tab of the Query Editor, click the drop-down arrow under the Close & Load command and select Close & Load To.
3. In the Load To dialog box, choose the option Only Create Connection, and then click the Load button.
4. Repeat Steps 1 through 3 for the other worksheets in the workbook.

After you’ve created queries for each region, open the Workbook Queries pane (choose Data ⇒ Show Queries) to see all queries. As you can see in Figure 11-8, each query is a connection-only query.

Now that your data is in queries, you can start appending.

Appending the data

In a perfect world, this section is where you would read about the nifty button that appends all your queries at one time. Unfortunately, Power Query doesn’t have a nifty button that lets you append many tables all in one shot. You can append only one table at a time.

To append data, follow these steps:

1. In the Workbook Queries pane, right-click on the NorthData query and select Edit to open the Query Editor.

2. On the Home tab of the Query Editor, click the Append Queries command.

   The Append dialog box opens.

3. The drop-down menu contains a list of all queries in the current workbook, as shown in Figure 11-9. The idea is to select the query you want to append to the query you’re editing. Select one of the region queries, and then click the OK button.
4. Repeat Steps 2 and 3 until you’ve appended the SouthData, MidwestData, and WestData queries.

5. After all queries have been appended, click the Close & Load command to save the data and exit the Query Editor.

   At this point, the NorthData query contains the data for all regions. To see the full consolidated table, you need to change the load destination of the NorthData query to the workbook instead of the connection only.

6. In the Workbook Queries pane, right-click the NorthData query and select Load To.

   The Load To dialog box opens.

7. Select the option for Table, and then click OK.

Figure 11-10 illustrates the final output. You’ve successfully created a consolidated table of region data.

Note in Figure 11-9 the option for NorthData (Current). You see the term (Current) next to the NorthData query because you’re editing that query. Be careful not to select any query with (Current) next to it. Otherwise, you’ll append the query to itself, effectively duplicating all records within the query. Unless you have some strange requirement where creating exact copies of records is beneficial, avoid appending the current query to itself.

As you append each query, you may be tempted to scroll down to the bottom of the data to see the newly added records. Unfortunately, the data preview in the Query Editor shows only a truncated sample set of records. Even if you scroll to the bottom of the preview, you’re unlikely to see the appended data.

Understanding Power Query joins

Similar to VLOOKUP in Excel, the Merge feature joins the records from one query to the records in another by matching on a unique identifier. An example of a unique identifier is Customer ID or Invoice Number.

You can join two datasets in one of several ways. The kind of join you apply is important because it determines which records are returned from each dataset.

Power Query supports six kinds of joins, as described in the following list and shown in Figure 11-11:

• Left Outer: Tells Power Query to return all records from the first query, regardless of matching, and only those records from the second query that have matching values in the joined field.
• Right Outer: Tells Power Query to return all records from the second query, regardless of matching, and only those records from the first query that have matching values in the joined field.
• Full Outer: Tells Power Query to return all records from both queries, regardless of matching.
• Inner: Tells Power Query to return only those records from both queries that have matching values.
• Left Anti: Tells Power Query to return only those records from the first query that don’t match any of the records from the second query.
• Right Anti: Tells Power Query to return only those records from the first query that don’t match any of the records from the second query.

Merging queries

To better understand the Merge feature, I’ll walk you through an exercise that merges interview questions and answers. In this walk-through, I use the predefined queries found in the Merging_Data.xlsx sample file available online at www.dummies.com/go/excelpowerpivotpowerqueryfd.

As you can see in Figure 11-12, two existing queries are in the Workbook Queries pane: Questions and Answers. These queries represent the questions and answers from the interview. The goal is to merge these two queries to create a new table showing questions and answers side-by-side.

The Merge feature can be used only with existing queries. That is to say, no matter what kind of data sources you have, you need to import them into Power Query before you can use them in a merge.

Follow these steps to perform the merge:

1. Choose Data ⇒ New Query ⇒ Combine Queries ⇒ Merge (see Figure 11-13).

   This step opens the Merge dialog box.

   In this dialog box, you use the drop-down boxes to select the queries you want to merge and then choose the columns that defined the unique identifier for each record. In this case, the InterviewID and QuestionID/AnswerID fields make up the unique identifier for each record.

2. Select the Questions query in the top drop-down box.
3. Hold down the Ctrl key on the keyboard, and then click InterviewID and QuestionID — in that order.
4. Select the Answers query in the lower drop-down box.
5. Hold down the Ctrl key on the keyboard, and then click InterviewID and AnswerID — in that order.
6. Use the Join Kind drop-down box to select the kind of join you want Power Query to use. In this case, the default, Left Outer, works.

7. Click the OK button to finalize and open the Query Editor.

   In Figure 11-14, note the small numbers 1 and 2 in the InterviewID and QuestionID fields. These small numbers are assigned based on the order in which you selected them (refer to Steps 3 and 5).

   The order in which you selected the unique identifiers in each query matters. The two columns tagged with the small number 1 will be joined regardless of column labels. The two columns tagged with the small number 2 will also be joined.

   At the bottom of the Merge dialog box, Power Query shows you how many records from the lower query match the top query, based on the unique identifiers you selected. In this case, about 17,600 answer records match the 26,910 question records.

   You don’t need a 100 percent match for the merge to be valid. There might be a good reason that the records in the two queries don’t all match up. In this case, not all questions were answered in all interviews, so the Answers query has fewer records.

8. With the new merged query open in the Query Editor, click the Expand icon in the NewColumn field and choose the fields you want included in the final output (as shown in Figure 11-15). In this case, just choose the Answer field.

   At this point, you can apply more transformations, if needed.

9. When you’re happy with the way things look, click the Close & Load command to output the results to the workbook.

   Figure 11-16 shows the final merged query.

If you need to adjust or correct a merged query, right-click the query in the Workbook Queries pane and select Edit. In the Query Editor, click the Gear icon next to the Source query step, as shown in Figure 11-17. This action opens the Merge dialog box, where the necessary changes can be applied.