Combining two identity fields can create a new collision even when the original pairs are different. For example, joining 1 and 23 without a boundary gives 123, just as joining 12 and 3 does. Test the representation before using it to link records.

Excel's Data Model relationships use one column from each table and cannot use a multi-column composite key directly. Microsoft documents combining the components into a single key first. The lookup column must be unique and have a compatible type with the matching source column; nulls and empty strings count as a blank value, so multiple blank lookup keys are not permitted.

Preserve the boundaries between components

For a disposable example, use two nonblank text fields, Site and Item, restricted to digits. Keep the pairs 1/23 and 12/3 as separate lookup records with descriptions Cable and Adapter. A pipe-separated representation produces 1|23 and 12|3, preserving their distinction under that explicitly restricted input rule.

With the components in A2 and B2, a worksheet helper formula =A2&"|"&B2 constructs the example key. Apply the same declared rule on both sides before importing or relating the tables. Retain the original fields beside the result so each combined value remains explainable.

Do not generalise that separator to unrestricted text without checking it. If either component can contain a pipe, different pairs can again produce the same output. Also decide whether leading zeros belong to the identity. Missing or ambiguous components need an explicit resolution rule before model loading, rather than an improvised replacement that silently links unrelated records.

Connect and reconcile the intended records

In a working Excel workbook containing the prepared tables, choose Data, Relationships, New. Set Table and Column (Foreign) to the detail table and its combined key. Set Related Table and Related Column (Primary) to the lookup table and its unique combined key, then confirm. Repeated foreign keys on the detail side can be legitimate.

Give the practice detail table two lines for 1|23 and one for 12|3. Use the lookup descriptions in a model-based report and inspect which detail lines contribute to Cable and Adapter. The intended result keeps both Cable lines while assigning the other line to Adapter; it must not merge both identities under 123.

Before accepting the real model, check the lookup's unique keys, unresolved components and unmatched detail keys. Preserve the representation rule with the workbook. A relationship that can be created still needs this record-level check to establish that it connects the intended identities.

Sources: Microsoft: Data Model relationships; Microsoft: supporting procedure.