Copilot in Excel With Data Model Tables: Relationship Inference Notes

When you use Copilot in Excel with data stored in Data Model tables, you may notice that Copilot automatically detects and uses relationships between those tables. This behavior is called relationship inference. It allows Copilot to combine data from multiple tables without you manually defining every relationship. However, inference rules have specific limitations and may not always match your intended logic. This article explains how Copilot infers relationships, what rules it follows, and how to avoid incorrect results.

How Copilot Infers Relationships in the Excel Data Model

Copilot in Excel relies on the underlying Data Model to understand how tables connect. The Data Model is an in-memory database that stores table structures, columns, and relationships. When you ask Copilot a question that spans multiple tables, such as “Show total sales by region,” Copilot examines the Data Model for existing relationships. If no explicit relationship exists, Copilot attempts to infer one based on matching column names and compatible data types. For example, a column named “CustomerID” in both a Sales table and a Customers table will trigger an inference, even if no relationship was manually created.

This inference process is not random. Copilot follows a set of heuristics:

• Column names must be identical or nearly identical, ignoring case and leading/trailing spaces.
• Data types must match or be implicitly convertible. For example, a text column named “OrderID” and a number column named “OrderID” will not be matched.
• Only one column per table is used for the inference. If multiple columns have the same name across tables, Copilot uses the first match found in the Data Model schema.
• Inferred relationships are always one-to-many, with the table containing unique values on the “one” side and the other table on the “many” side.

Copilot does not persist inferred relationships in the Data Model. They exist only during the query session. This means that each time you open the workbook or refresh the Data Model, Copilot re-evaluates the inference. If your data changes, the inferred relationship may change as well.

Steps to Verify and Control Relationship Inference

To ensure Copilot returns accurate results, you should verify which relationships it uses and, if necessary, define them explicitly. Follow these steps.

1. Open the Data Model in Power Pivot
   Go to the Power Pivot tab and click Manage. This opens the Power Pivot window where you can see all tables and relationships in the Data Model.
2. Switch to Diagram View
   In the Power Pivot window, click Diagram View on the Home tab. This displays tables as boxes with columns and lines representing relationships. Explicit relationships appear as solid lines. Inferred relationships are not shown as lines.
3. Check for Missing Relationships
   Look for tables that are not connected by a line. If two tables that should be related are disconnected, Copilot will attempt inference but may choose the wrong columns. To prevent this, create an explicit relationship.
4. Create an Explicit Relationship
   In Diagram View, drag a column from one table to the matching column in another table. A dialog appears. Confirm the relationship direction and cardinality. Click OK. The line now appears solid, and Copilot will use this explicit relationship instead of inferring one.
5. Test Copilot With a Multi-Table Query
   Return to Excel and type a question in the Copilot pane that requires data from both tables, such as “List products with total quantity sold.” Verify that the output matches your expected join logic.

Common Inference Issues and How to Avoid Them

Copilot Matches the Wrong Columns

If two tables have multiple columns with the same name, Copilot may pick the wrong one. For example, a Sales table may have a “Date” column for order date and a Products table may have a “Date” column for product launch date. Copilot might infer a relationship on “Date” even though the intended join is on “ProductID.” To fix this, rename one of the duplicate columns before adding the table to the Data Model, or create an explicit relationship on the correct columns.

Inferred Relationship Causes Duplicate Rows

Because Copilot always infers one-to-many relationships, it may assume a unique column on the “one” side that actually contains duplicates. This can cause row multiplication in query results. For instance, if both tables have a “CustomerID” column but the Customers table has duplicate entries for the same ID, Copilot will produce inflated counts. To prevent this, ensure that the lookup table has unique values in the column used for the relationship.

Copilot Cannot Infer Relationships in New Worksheets

If you add a new table to the workbook by copying data into a worksheet, Copilot does not automatically add it to the Data Model. Relationship inference only applies to tables explicitly added to the Data Model via Power Query or Power Pivot. To include a new table, go to the Power Pivot tab and click Add to Data Model before asking Copilot questions that involve that table.

Explicit Relationship vs Inferred Relationship: Key Differences

Item	Explicit Relationship	Inferred Relationship
Creation method	Manually defined in Power Pivot Diagram View or Power Query	Automatically guessed by Copilot based on column names and data types
Persistence	Saved in the Data Model and persists across sessions	Exists only during the current Copilot query session
Cardinality control	User can set one-to-one, one-to-many, or many-to-many	Always one-to-many, with Copilot deciding the direction
Visibility	Visible as solid lines in Power Pivot Diagram View	Not visible in any Excel interface
Reliability	High, because the user explicitly defines the join columns	Moderate, depends on column naming conventions and data uniqueness

Use explicit relationships when you need consistent and predictable results. Use inferred relationships as a quick start but always verify the output for accuracy.

You can now manage Copilot’s relationship inference in Excel by verifying existing Data Model connections and creating explicit relationships where needed. Next time you build a report with multiple tables, open Power Pivot Diagram View first to confirm all joins are correct. For advanced scenarios, consider using Power Query to merge tables before loading them to the Data Model, giving you full control over join types and column selection.