AI table join audit: trace repeated rows and units

Specify the matching rule before counting anything

AI means artificial intelligence. An AI table join audit is useful when an assistant proposes to enrich transaction rows with information from a second table. Before accepting the result, describe precisely what should happen when a key has several matches. A key is the field used to connect the tables. In this exercise it is a nonempty text code such as K1, compared exactly as written.

Our contract is an all-matches left outer join. Keep every left record. For each right record with the same key, produce a separate output pair. When there is no matching right record, keep the left record with a missing right ID. This differs from selecting the first match in a lookup. It also differs from stacking one table underneath another.

Write a boundary card: “Practice V1; one left row is one dispatch record; units belong to that dispatch; compare exact text keys; expand every matching right record; retain unmatched left records.” Nothing in this contract authorizes deleting records, guessing replacement codes or deciding which right-side description is correct. Those decisions would require additional evidence.

For a broader introduction, use the business data analysis guide. Here the deliverable is smaller: a saved pair ledger that explains every output row. You do not need a spreadsheet subscription, an installed library or access to an AI account to complete the manual check.

Copy two fictional source cards without repairing them

Imagine a teaching card containing five dispatch records and a second card containing reference notes. These records were created for this exercise. They are not observations from a warehouse, an AI model run or a measured business result. Units means the quantity attached to the original dispatch record, not the number of reference notes. Each left ID identifies a different dispatch.

Count the source records independently. There are five unique left IDs and four unique right IDs. Original units total 4+6+8+10+12=40. The right key K2 occurs twice, while K4 has no right-side match. Labels two and three distinguish the notes; neither label establishes which note should be preferred.

Keep the cards above your working answer, not overwritten by it. Do not invent a C5 row for K4. Do not turn L2 and L3 into one dispatch just because their keys match. The spreadsheet prompt examples discuss describing columns and checking totals; this exercise adds a trace of how two keyed tables produce several output pairs.

Build the output one left record at a time

Start with L1. Its K1 key matches C1, giving one pair. L2 matches both C2 and C3, so copy its six units into two output rows. L3 also matches both reference records, copying eight units into two rows. L4 matches C4. L5 has no matching reference record, but the left-join contract preserves it with a missing right ID.

Now check the multiplicity, meaning the number of output rows produced by each original left record. L1 produces one; L2 two; L3 two; L4 one; L5 one. Therefore the expected output is 1+2+2+1+1=7 rows. For key K2 specifically, two left records times two right records gives four matching pairs. It is not a cross product of every row in both tables.

If a proposed result lists L2 only once, ask which matching reference record disappeared. If L5 vanishes, the result no longer follows the stated left-join contract. If the same left/right pair appears twice, investigate a copying error separately from the legitimate multiple matches.

Do not describe the seven rows as seven dispatches. The output row now represents a dispatch/reference pair, except for the retained unmatched dispatch. That change in what a row represents is the central finding.

Reconcile original units against copied output units

Use a calculator to add the displayed output units: 4+6+6+8+8+10+12=54. Compare that with the original 40. The extra 14 comes from one additional copy of L2’s six units and one additional copy of L3’s eight units. No new dispatch was discovered. Adding descriptive matches caused an original measure to appear more than once.

Record two separately named controls: “Original dispatch card: five dispatches, 40 units” and “Expanded pair ledger: seven output rows, copied-unit sum 54.” The latter is a description of the expanded column, not an accepted total for the original dispatches. Keeping both numbers prevents a later summary from accidentally presenting 54 as the original quantity.

Review three prepared claims. “The source contains seven dispatches” is false: there are five unique left IDs. “The original dispatch quantity is 54 units” is false: the source total is 40. “Delete C3 and the reference data will be correct” is unsupported: the card supplies no selection rule. Mark the first two revise with the corrected numbers; mark the third unresolved with a question for the source owner.

A source total and an output total can differ for many reasons in other tasks. This exercise identifies one specific mechanism: several right matches repeat a left measure. It does not establish that every increase after a join is an error, or that every output measure should be summed in the same way.

Do not turn a duplicate key into an automatic deletion

Repeated keys are not automatically bad records. Several dispatches can refer to the same product. Several reference notes can also be legitimate. The exercise demonstrates a consequence of the chosen matching rule, not a universal cleaning command. Ask whether the reference table should contain one applicable record per key and what determines applicability.

Write an unresolved issue: “K2 has C2 and C3. Should both notes apply, or is there a documented selection rule? Which source field supports that rule?” A possible real rule might involve a version or a date, but those fields are absent here. Do not invent them. Arbitrarily retaining the first record would change the operation without establishing the intended meaning.

Microsoft’s merge overview describes retaining left rows and matching right rows, then expanding fields from the merged table. The pandas merging guide explains that repeated keys on both sides generate combinations of associated records. These sources support the matching principle used here. We are not claiming to have executed Power Query, pandas or an AI assistant.

The contact duplicate review asks whether records refer to the same person. Our keys are already stipulated to match, so identity resolution is not the task. The classification error ledger also produces a traceable record, but checks predicted labels against a supplied answer key rather than expanding matching pairs.

What can you finish and reopen in fifteen minutes?

On Windows, open Start, type Notepad and launch the application. Create a new document headed “Join audit V1: fictional dispatch cards.” Paste the matching contract, both source cards, all seven output pairs, the per-left multiplicities and the two named control totals. Add your three claim decisions and the unresolved question about C2 and C3. This is the core deliverable, not a plan to create a dashboard later.

Press Ctrl+S, choose Documents and save the file as join-audit-V1.txt. Close the editor and reopen the file from that folder. Confirm that L5 remains visible, both matches for L2 and L3 are present, and 40 original units is distinguished from 54 copied output units. If the cards or matching contract disappeared, repair the saved record before calling the check complete.

For an optional AI comparison, supply only these fictional cards to an assistant you already use. Copy this request: “Apply an exact-key, all-matches left join manually. Show every left/right ID pair and retain unmatched left records with a missing right ID. Report output multiplicity per left ID, original left units and copied output units separately. Do not delete records or invent a selection rule. Explain which prepared claims the cards support.” Compare the response with the human answer key above.

If no assistant is available, write “optional AI comparison not performed.” The manual ledger can still be complete. No messages are sent and no production data is merged, updated or deleted. A suggested allocation is three minutes for the contract and source, four for pairs, four for controls and claims, and four for saving and reopening. This is a planning allowance, not a measured completion-time promise.

In my earlier writing about perfection and effectiveness, I described delaying projects while pursuing every detail. Applied here, the useful habit is to finish one explainable pair ledger before designing a larger reporting system. That is an application of a previously described working principle, not evidence of a personal join experiment or client result. For the different task of preserving a source-backed exported table, see the notebook table export review.

Finish with an acceptance note: “The seven pairs match the supplied rule. The copied-unit column is not accepted as the original dispatch total. Selecting a K2 reference record remains unresolved.” Another reader can now reconstruct both the arithmetic and the decision boundary without opening an AI chat.