Merge

As discussed earlier, when you merge queries you combine them horizontally; as a result, you get a wider table. When merging, you need a set of keys—columns that have matching values in both tables—telling Power Query which rows of the first table should be combined with which rows of the second table.

Like with the Append Queries feature, you can either merge two queries without creating a new one or you can merge queries as new.

There are six kinds of joins in Power Query:

  • Left Outer All from first, matching from second
  • Right Outer All from second, matching from first
  • Full Outer All rows from both
  • Inner Only matching rows
  • Left Anti Rows only in first
  • Right Anti Rows only in second

You can see the visual representation of joins in Figure 1-33.

 FIGURE 1-33 Joins available in Power Query

In case your data isn’t perfect, Power Query allows you to use fuzzy matching when performing merges. You can use the following options:

  • Similarity threshold Can be valued from 0 to 1, where 0 will make all values match each other and 1 will allow exact matches only. The default is 0.8.
  • Ignore case Lowercase and uppercase letters will be treated as the same.
  • Match by combining text parts Power Query will try to combine separate words into one to find matches between keys.
  • Maximum number of matches This option will limit the number of rows from the second table that are matched against the first table, and it can be useful if you expect multiple matches.
  • Transformation table You can use a column with two columns—From and To—to map values during the matching process. For example, you can map NZ to New Zealand, and the two values will be considered the same for merge purposes.

For Wide World Importers, let’s merge the City and State Province queries using the following steps:

Select the City query in the Queries pane.

Select Combine > Merge Queries on the Home ribbon.

Select State Province in the drop-down list below the City query preview.

Select State Province Key in both tables. The columns should be highlighted as in Figure 1-34.

FIGURE 1-34 Merge options

Ensure Left Outer is selected from the Join Kind drop-down list.

Select OK.

After you complete these steps, you’ll see a new column named State Province added to the City query. Note that the new column

  • Is of type Table
  • Has Table hyperlinks in each cell
  • Has a double-arrow button instead of a filter button on its header

If you select any cell in the new column without selecting the hyperlink, you’ll see a preview of the cell contents, and you’ll see a table row in our case.

You can expand the new column by selecting the double-arrow button on its header. There are two ways to expand a table column:

  • Expand You can select the columns from the joined table that you want to add to the current table. If there is more than one matching row in the joined table, the current table’s rows will be duplicated after expansion.
  • Aggregate This option aggregates rows and won’t duplicate any rows in the current table. You can apply arithmetic and statistical functions to the columns of the joined table. For example, if it made business sense, you could take the average of State Province Key from the State Province table.

When expanding the table column, you can use the original column name as a prefix. This option can be useful if there are column names that are the same in both tables but their content is different. For instance, if you are merging the Product and Product Category tables and both have a column called Name, then you can use the original column name as a prefix to avoid confusion.

In our example, let’s expand the merged column by completing these steps:

In the City query, select the double-arrow button on the State Province column header.

Clear the Select All Columns check box.

Select the State Province and Sales Territory check boxes.

Clear the Use original column name as prefix check box.

Select OK.

Rename the State Province.1 column to State Province.

Remove the State Province Key column.

The result should look like Figure 1-35.

 FIGURE 1-35 City query with columns from the State Province query

You’ve merged the City and State Province queries, and you’ve got five useful columns as a result. You don’t need the State Province Key column anymore, and the columns you didn’t include from the State Province query all had one distinct value each.

As is the case for the Targets for 2022 query, the information from the State Province query now appears in two queries, so there’s some data duplication in our queries. Again, we’ll address this later in the chapter.