Power Query: Append, Merge and Refresh

The problem

Orders for the first and second quarter come from two extracts. They hold the same information, but the second one was produced after a system update: the columns are in a different order and there is a new Channel column. The orders carry a pack code and a market code, but not the product name. And the regulatory team asks a question that no single table can answer: were any packs sold in a market where the company holds no marketing authorization record for them?

Answering means combining four tables. Two of them must be stacked, one must add columns by key, and the last must be compared on two columns at once. With formulas, that is a copy-paste, a block of lookups and a helper column. With Power Query, it is three steps that rerun on every refresh.

Append and merge

Power Query combines tables in two ways, and the distinction is the same as in any database.

Append stacks rows. The tables have the same logical structure, such as orders from different periods, affiliates or systems, and the result has the rows of all of them. The folder connection, which combines every file in a folder, is an append done automatically on files with the same structure.

Merge adds columns. A fact table, such as orders, is matched to another table through a common key, such as the pack code, and gains columns from it. It is the equivalent of a lookup applied to a whole table at once, with one difference that matters: a merge can also keep or drop rows depending on whether a match exists.

In the companion file, all four sources are Excel tables. The quickest setup is to create a connection for each one: click in the table, choose Data > From Table/Range, then Close & Load To > Only Create Connection. The combining queries are then built on those connections.

Append: stacking tables

Data > Get Data > Combine Queries > Append opens the dialog from the worksheet; inside the Editor, the same command is Home > Append Queries, with a variant Append Queries as New. The first adds rows to the current query; the second creates a new query and leaves the sources unchanged, which is almost always the better choice, because each source stays available for its own checks. The dialog has two modes: Two tables, the default, and Three or more tables, where you pick the tables from a list in the order you want them stacked.

The essential rule is how columns are matched: by name, not by position. Microsoft's documentation states it plainly, and the companion file shows it. OrdersQ2 has Market before Pack Code, yet the appended query lines them up correctly. Channel, which exists only in the second table, appears in the result, filled with null for the first-quarter rows. The appended query has 85 rows, 40 plus 45.

The consequence is a trap as much as a feature: a column named Pack code in one table and Pack Code in the other, or Net Sales and Net sales value, becomes two half-empty columns. Rename columns in each source query before appending, so the names match exactly.

Sources do not need to be of the same type. A CSV file, a workbook table and a database view can be appended, as long as each one, after its own cleaning, produces the same columns.

Merge: adding columns by key

In the appended query, renamed Orders, choose Home > Merge Queries (or Merge Queries as New). The dialog shows the current table at the top; choose the second table, Packs, from the drop-down, then click the key column in each: Pack Code in both. At the bottom, a message shows how many rows match, an early warning worth reading every time: 85 of 85 here. A lower number means codes that are missing from the master or written differently, such as with a trailing space.

The Join Kind decides which rows are kept:

Left Outer covers most everyday merges: start from the fact table and add descriptions from a master. The anti joins are the auditor's tools: they return the exceptions.

After the merge, the query has a new column named after the second table, whose cells say Table: each holds the matching rows, nested. Click the expand icon in the column header, tick the columns to bring in (here Product and Pack Size), and untick Use original column name as prefix unless you want names such as Packs.Product. Leave out the key, which is already in the first table.

The merge is declarative. If the pack master later gains a column, such as the therapeutic area, there is no need to redo the merge: edit the expand step, tick the new column, and every refresh includes it.

Two keys at once: the authorization check

The regulatory question needs a match on two columns: a marketing authorization belongs to a pack in a market. In the Merge dialog, click Pack Code in the first table, then Ctrl+click Market; small numbers 1 and 2 appear next to the headers. Do the same, in the same order, in MARegister. Columns are paired by that order, not by name, so the sequence matters, and paired columns must have the same data type.

With Join Kind set to Left Anti and Merge Queries as New, the result is the list of orders with no matching authorization record: eight orders, on three pack and market combinations. The Check sheet confirms the count with classic COUNTIFS formulas on the sources. Loaded to a sheet, this query becomes a standing exceptions report: empty when everything is in order, populated the moment a problem appears.

For keys typed by hand, such as company or product names, the Merge dialog also offers Use fuzzy matching to perform the merge, which pairs values that are similar rather than identical. It works only on text columns and needs a careful review of the matches, but it can save hours on master data with inconsistent spellings.

Refresh

A query is a recipe, not a result. Data > Refresh All reruns every query in the workbook on the current sources: the new order typed into OrdersQ2 appears in Orders and, if it concerns an unregistered combination, in the exceptions list. Right-clicking a single query in Queries & Connections refreshes only that one.

The refresh options are in the query properties: in Queries & Connections, right-click the query > Properties, tab Usage. Four of them matter:

• Refresh data when opening the file, the simplest automation: the workbook is current every time someone opens it;
• Refresh every n minutes, for sources that change during the day;
• Refresh this connection on Refresh All, to exclude a slow or static query from the general refresh;
• Enable background refresh, which lets you keep working while the query runs.

Background refresh has a side effect worth knowing. A macro that runs Refresh All and then reads the results may read them before the refresh is complete. If a macro depends on the data, turn background refresh off for those queries so that Excel waits. Queries that load to the Data Model cannot refresh in the background anyway.

When a refresh finds cell errors, Queries & Connections shows a link with the number of errors under the query name. Clicking it creates a query that lists the faulty rows, a quick route to the source of the problem.

When Power Query is the right tool

Power Query pays off where work repeats: monthly exports, periodic files, the same cleaning and combining applied again and again. Calculations that change often, such as scenarios and sensitivities, belong in formulas, where they can be edited in a cell. Tasks that leave the worksheet, such as sending emails or moving files, belong to VBA. And models with relationships between several tables use Power Query to import and clean, then the Data Model to relate and measure. The useful question is not whether to use Power Query, but which part of the flow to move into it. Almost always, the answer is import and preparation.

Common mistakes

• Appending tables whose column names differ slightly. The result has duplicate, half-empty columns.
• Using Append Queries instead of Append Queries as New. The first source query is modified and no longer shows its own rows.
• Ignoring the match count in the Merge dialog. A low number signals inconsistent keys before any wrong total reaches a report.
• Selecting composite key columns in a different order in the two tables. The join pairs the wrong columns.
• Expanding the key column again after a merge. It duplicates the column already in the first table.
• Leaving background refresh on for queries that a macro reads right after refreshing.

For further reading

• Append queries - Microsoft Learn
• Merge queries overview - Microsoft Learn
• Refresh an external data connection in Excel - Microsoft Support