Clean, merge & append

Append stacks; merge matches

January has three orders and February has two orders with the same fields. Append adds their records into a longer table. It matches columns by name, so check spelling and data types rather than relying on column position. A field absent from one input produces null values for those records.

A product master has one row per SKU and a Category field. Merge matches each order's SKU to that master so Category can be added to the order. It combines information by a key rather than simply adding more order rows.

Neither operation verifies that the business meaning is correct. Keep each table's grain explicit: one row per order line, product or monthly total.

Worked example: combine two months

Create two Excel tables with headings Order, SKU and Sales. January contains O101/P101/100, O102/P102/50 and O103/P103/80. February contains O104/P101/40 and O105/P102/70.

1. Import both tables as queries and inspect their data types.
2. In Power Query Editor choose Home → Append Queries → Append Queries as New.
3. Select the January and February queries and name the result AllOrders.
4. Confirm 5 records and 340 Sales.
5. Keep the month or source-file field if the report must trace provenance.

If monthly files contain overlapping order lines, append retains those duplicates. Do not remove them until a valid unique key and duplicate rule have been established.

Add category with a left outer merge

1. Create a Products table with SKU/Category: P101/Accessories, P102/Travel and P103/Office.
2. Import it as a query and confirm that SKU is unique and Text.
3. Select AllOrders and choose Home → Merge Queries.
4. Select SKU in both tables, in the same order if a composite key uses several columns.
5. Choose Left Outer to retain every AllOrders record.
6. Expand only Category from the nested match column.
7. Confirm 5 rows and total Sales still 340.

An Inner join keeps only matches. A Left Outer join keeps the left records and uses null for unmatched attributes after expansion. A Left Anti join can create an exception list of orders without a product match. Choose the rule deliberately rather than hiding missing categories.

Detect one-to-many expansion

Suppose January's product master accidentally has two P101 rows. O101 then matches twice. Expanding both matches turns the original three January records into four rows and increases repeated Sales from 230 to 330.

The product attributes may look reasonable, yet any later sum is overstated. Before merging, group the master by SKU and count rows to find duplicate keys. After merging, compare record counts and total Sales with the pre-merge baseline.

When duplicate keys represent valid history or several suppliers, removing duplicates arbitrarily loses meaning. Add the necessary date, version or supplier condition, or aggregate to the intended grain before matching.

Make a refreshable cleanup sequence

A useful sequence is: keep required fields, standardize text keys, set types, check exceptions, append compatible records, merge validated attributes, then group or summarize at the report grain. Step order matters: a numeric conversion that already removed leading zeros cannot be repaired by trimming text afterward.

For a summary, Home → Group By can group AllOrders by Category and sum Sales. Accessories is 140, Travel 120 and Office 80; these reconcile to 340. Save the detailed prepared query too, so a reader can investigate the summary.

These are native Power Query activities. The browser questions check the combination logic and reconciliation; query authoring, joins and refresh are not simulated here. Platform capabilities follow the previous lesson's compatibility notes.