Learning goals

Choose correctly between Append Queries and Merge Queries
Merge the Service Lines mapping onto the billing export on the service code
Expand a merged column without dragging duplicate rows into the table
Rebuild the Invoice Amount check and confirm it matches every loaded amount

Concepts

Two different questions

Append and merge get confused because both live on the Home ribbon's Combine group, and they answer different questions. Append stacks rows: 'more of the same'. You append January to February, or Northgate's Ohio invoices to its Michigan invoices -- tables with the same columns. Merge joins columns: 'more about the same rows'. You merge a service-line name onto an invoice, or a customer's credit terms onto an order, matching on a key that exists in both tables.

The fastest way to pick: if the result should have MORE ROWS, append. If it should have MORE COLUMNS, merge. Northgate appends monthly CRM files inside the folder import (lesson 2) and merges the Service Lines mapping into Invoice Facts (this lesson).

=VLOOKUP("MS",'Service Lines'!$B$5:$C$8,2,FALSE)

The worksheet version of the merge, so the concept is concrete: look up the code MS in the Service Lines table and return Managed Support. FALSE forces an exact match -- the same discipline the merge's join key needs. Power Query does this as a merge step instead, at any table size, without counting columns.

Merge on a clean, exact key

The billing export's Service Code column arrives as 'MS ', 'tr', 'Ds', 'ms', 'cl', and 'Tr'. None of those match the Service Lines codes MS, CL, DS, TR exactly -- so before the merge, the query trims (Text.Trim) and upper-cases (Text.Upper) the column. Merge on a key that has not been standardized and you get nulls that look like missing data.

The clicks: Home > Merge Queries, pick Service Lines as the other table, click Service Code here and Code there, Join Kind Left Outer (keep all invoices, bring in matches). Then expand the merged column with only Service Line checked and 'Use original column name as prefix' cleared. The script on the M Code Reference sheet is exactly those three steps: Table.NestedJoin, Table.ExpandTableColumn, and the trim and upper-case that make the join work.

💡 Tips:

Left Outer keeps every invoice even when the code is wrong -- which is what you want, because a missing service line should be visible, not silently dropped.

After any merge, filter the expanded column for nulls once: those are unmapped codes, and every one of them is a master-data bug worth fixing on Service Lines.

The amount check

Invoice Facts carries Hours and Rate from Ledgerly and an Invoice Amount computed from them. The check formula multiplies the two loaded numbers and rounds to cents; if a row ever disagrees with the loaded amount, that row is wrong -- no exceptions. This is the kind of cross-foot a query output should invite: it survives refresh because it reads the table, not a transcription.

The output also carries the NET 30 due date (lesson 7 builds it), the paid date and paid amount typed with the en-GB locale from lesson 3, and the merged Service Line on every one of the thirteen rows. Six invoices are settled, seven are open, and exactly one of those open rows -- INV-2404, Harborview Cafe LLC, Data & Security, 930.00 -- is overdue.

=ROUND($G5*$H5,2)

The Invoice Amount check: hours in G5 times rate in H5, rounded to cents. For INV-2401 that is 14 x 145 = 2,030.00, matching the loaded amount. ROUND keeps floating-point noise out of a money column -- 12.345 must never become 12.349999.

Practice

The exercise opens on the Invoice Facts output (AFTER); the BEFORE state is the Billing Export tab and the merge target is Service Lines. Cell I5 is blank -- you retype the amount check. Nothing is saved in the browser.

Audit the merged column — Column F carries a service line on all thirteen rows: Managed Support (MS), Cloud Migration (CL), Data & Security (DS), Training & Enablement (TR). Compare with Billing Export column D, where the same codes arrived as 'MS ', 'tr', 'Ds', 'ms', 'cl', and 'Tr' -- the trim and upper-case steps are what made the join land.
Retype the amount check — Cell I5 is empty. Type =ROUND($G5*$H5,2) and press Enter: 2,030.00 for INV-2401. Work down the column in your head -- every row multiplies out to the loaded amount, from 920.00 (INV-2402) to 3,960.00 (INV-2406).
Find the one overdue row — Column M (Status) reads Paid six times and Open seven times, with exactly one Overdue: row 8, INV-2404, Harborview Cafe LLC, open balance 930.00. Its due date (Aug 29, 2026) is before the Aug 31, 2026 close -- the status logic itself is lesson 7's build.
Reconcile the ledger — Sum column I across all thirteen rows: 24,805.00 invoiced lifetime-to-date in this ledger. Of that, 14,390.00 has been collected and 10,415.00 is still open -- the same 10,415.00 the report will quote as open receivables.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

☐ Retyped =ROUND($G5*$H5,2) into I5 and got 2,030.00 for INV-2401
☐ Confirmed all 13 rows carry a merged service line and can explain why the trim and upper-case steps had to come first
☐ Verified the baseline: 930.00 overdue (INV-2404) and 10,415.00 open receivables across seven unpaid invoices

⚡ Lesson 06 · Append versus merge