Level 4 · Merge a discount table, then price every line

In a worksheet you bring a value across from another table with XLOOKUP or VLOOKUP, one formula per row. Power Query does the same job for the whole table at once with Merge Queries. Chloe’s discount list is a second query; the order lines are the first. Merge matches them on the customer code.

Two queries, one key

The order lines have 25 rows for 8 business customers. The Discounts query has 6 rows:

customer_code	discount
C01335	0.10
C01355	0.05
C00012	0.10
C01404	0.05
C00288	0.10
C00534	0.05

Four of these customers ordered in Sept 1–10. C00288 and C00534 are on the list but did not order, which is normal: a lookup table can hold keys you won’t need. Four customers who did order (C00528, C01334, C00026, C00607) have no contract.

Merge queries: Discounts on customer_code, expand discount

In Excel: Home → Merge Queries, pick Discounts in the second drop-down, click customer_code in both tables, keep Join Kind: Left Outer (all from first, matching from second), OK. Power Query adds a column full of Table values; click the expand icon on its header, tick discount, untick “Use original column name as prefix”, OK.

Left outer: every line stays

A left outer join keeps every row of the first query. When a key has no match, the new columns are null, Power Query’s empty value. That is the same situation as an XLOOKUP returning #N/A, except that null does not shout. Here 12 of the 25 lines come back with a null discount.

The match is exact and case-sensitive: c01335 would not find C01335, and a code with a trailing space would not either. When a merge finds fewer matches than you expect, check the keys with Trim and Uppercase steps on both sides. The Merge dialog helps: at the bottom it says how many rows matched (“The selection matches 13 of 25 rows”).

Null is not zero

Replace values: discount null with 0

In M, arithmetic with null gives null: 100 * (1 - null) is null, not 100. So before the total, turn “no contract” into an explicit 0. In Excel: select the discount column, Transform → Replace Values, Value To Find null, Replace With 0.

Add column: total_after_discount = [line_total] * (1 - [discount])

On the first line, C01335’s Vale Enamel Bowl Set: 11.99 × (1 − 0.10) = 10.79. On order 10816, C00528 has no contract, so the line keeps its 53.97.

The M behind the steps

#"Merged Queries" = Table.NestedJoin(Source, {"customer_code"}, Discounts, {"customer_code"},
    "Discounts", JoinKind.LeftOuter),
#"Expanded Discounts" = Table.ExpandTableColumn(#"Merged Queries", "Discounts", {"discount"}, {"discount"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Discounts", null, 0, Replacer.ReplaceValue, {"discount"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "total_after_discount", each [line_total] * (1 - [discount]))

A merge is two steps in real Excel, the join and the expand. This practice editor folds them into one line.

Common mistakes

• Skipping the null replacement. 12 lines get a null total, and every sum built on them is too low.
• Choosing Inner join. It keeps only the 13 lines with a contract and silently drops the rest of the orders.
• Duplicate keys in the lookup table. If a customer appeared twice in Discounts, Power Query would repeat that customer’s order lines once per match. (This practice editor takes the first match; real Excel duplicates rows.) Remove duplicates on the key in the lookup query first.
• Typing the discount as 10 instead of 0.10. 1 - 10 is −9. Know whether a rate is a fraction or a percentage before you multiply.