Company reports rarely come from one system. Invoices come from the finance system, customer details from the CRM, exchange rates from treasury. Each system writes its keys its own way, and a merge only matches values that are exactly equal, type included.
Before you merge, make the keys agree:
- Same type. The text " 00417" and the number 417 never match. Convert both sides, for example with Number.From, which also ignores the spaces.
- Same case and spacing for text keys: Text.Upper, Text.Trim.
- One row per key on the lookup side. If a customer appears twice in the CRM export, every one of their invoices appears twice after the merge. Table.Distinct(Customers, {"CustomerID"}) keeps one.
Pick the join kind from the question. LeftOuter keeps every invoice, with nulls where no customer matched. LeftAnti returns only the invoices without a match, which is the list to send back to the CRM team. Each source here has its own staging query (Customers, Invoices, FxRates); the report query references them.
Both keys become whole numbers before the merge, so " 00417" in the CRM matches 417 in the invoices.