Chapter 40: Making Queries Work Together
4040
FIGURE 40.10
The final consolidated table of all regional dataNote in Figure 40.9 that the NorthData query is on both the left and right list boxes. Be careful not to move the
NorthData query to the right list box by mistake. If you do, you’ll append the query to itself, effectively duplicating all
of the records within the query. Unless you have some strange requirement where creating exact copies of records is
beneficial, you will want to avoid appending the current query to itself.
Beware of Mismatched Column Labels
When you append one query to another, Power Query first scans the column labels for both queries
to capture all column names. It then outputs all distinct column names and consolidates the data from
both queries into the appropriate columns. It uses the column labels as a guide for knowing which
data goes into which column.
If the column labels in your queries don’t match, Power Query will consolidate data for any matching
column and will leave null values in any columns that don’t match.
For example, imagine you have one query with the column labels Region and Revenue and another
query with the column labels Region and SalesAmount. Appending these two records will yield a final
table with all three columns: Region, Revenue, and SalesAmount. The records from the first query
will be input into the Region and Revenue fields. The records from the second query will be input
into the Region and SalesAmount field. This will essentially leave you with gaps in the Revenue and
SalesAmount fields.
The bottom line is to make sure the column labels in your queries are identical before appending. As
long as the column labels in each query are identical, Power Query will be able to append the data
correctly. Even if the columns in each query are positioned in a different sequence, Power Query is
able to use the column labels to get all the data into the correct columns.