Power Query does not create relationships between tables; it imports and transforms data. To relate tables in Excel, load them into the Data Model, then create a relationship between matching columns, such as ProductID in a Products table and ProductID in a Sales table.
What Excel Power Query relationships mean
The phrase can refer to two different parts of Excel that work together. Power Query connects to data sources, cleans and reshapes data, and loads the results. The Excel Data Model stores tables and lets Excel use relationships between them. A relationship tells Excel how rows in one table correspond to rows in another; it does not merge the tables or duplicate their columns.
For example, a sales file may contain one row per transaction, including a ProductID but not the product name or category. A separate Products table may contain one row per product, with its name and category. A relationship on ProductID lets a PivotTable combine sales amounts with product names and categories without copying those descriptive fields into every sales row.
In short, Power Query prepares the tables, while the Data Model relates them for analysis. They are often used in the same workflow, but they are not the same feature.
How relationships work in the Data Model
A relationship connects a column in one model table to a column in another. Usually, the column on the lookup side contains one unique value per item, while the matching column on the transaction side repeats values. This is commonly called a one-to-many relationship: one product can appear in many sales rows.
When you build a PivotTable from the Data Model, Excel uses the relationship to combine fields from the connected tables. A filter on the lookup table can filter related records in the transaction table. For example, choosing a product category can limit the sales rows included in a total. The relationship is based on the matching values, not on the tables’ positions in a worksheet or the order in which they were imported.
Excel relationships require compatible columns and a unique key on at least one side. The columns should represent the same kind of identifier and use compatible data types. A ProductID stored as text in one table and as a number in another can prevent a relationship from being created or produce unexpected matches.
Prepare and load the tables with Power Query
Start by importing each source as its own query. In Excel for Microsoft 365 on Windows, use Data > Get Data and choose the relevant source, or select an existing table or range through Data > From Table/Range. In the Power Query Editor, check that key columns have consistent types, remove unwanted rows, and give columns clear names.
To put query results in the Data Model, use Home > Close & Load > Close & Load To… in the Power Query Editor. In the load options, choose Only Create Connection and select Add this data to the Data Model. Depending on the workflow, you can also load a query to a worksheet and add it to the model. Repeat for every table you need to relate.
- Prepare the lookup table so its key column has no duplicate values. For Products, that means one row per ProductID.
- Prepare the detail table, such as Sales, with a matching ProductID on each relevant row.
- Load both query results into the Data Model. Loading a query only to a worksheet does not automatically make its worksheet table available as a model table.
- Open Data > Relationships, select New, and choose the two tables and the matching columns. Confirm the relationship.
- Create a PivotTable using the workbook’s Data Model, then try fields from both tables to confirm that the relationship behaves as intended.
If the Power Pivot tab is available, its Manage and Diagram View tools can also show the model tables and their connections. The Data > Relationships command is a straightforward way to create or inspect relationships in Excel for Windows.
A concrete example: Products and Sales
Suppose Products has ProductID, ProductName, and Category, with one row for each product. Sales has OrderDate, ProductID, Quantity, and SalesAmount, with many rows for the same product. Create the relationship between Products[ProductID] and Sales[ProductID]. Products is the lookup side because each ProductID appears once there; Sales is the many side because a product can be sold repeatedly.
In a PivotTable, place Category from Products in Rows and SalesAmount from Sales in Values. Excel can summarize sales by category through the relationship. You can also add ProductName to Rows to see a more detailed breakdown. Without the relationship, Excel does not know that the ProductID in one table identifies the product described in the other.
A useful extension is a separate calendar table with one row per date, related to the order-date column in Sales. A dedicated date table can make date-based reporting more consistent, especially when the source has missing dates or covers only dates on which transactions occurred.
Relationship versus Merge Queries
Merge Queries is a Power Query operation; a Data Model relationship is a connection used after tables are loaded. A merge joins columns from two queries into a new query result. Depending on the join type, it can bring product names into sales rows, filter rows, or retain unmatched rows. The merged result is then loaded as a table.
A relationship keeps the source tables separate in the model. It is often a better fit when you want a PivotTable to use fields from several tables without building one large repeated table. A merge can be useful when you need a flattened output for export, or when a downstream tool requires one table. Neither approach is universally better; the choice depends on how the result will be used.
Appending queries is different again: it stacks rows from tables with corresponding columns. It does not match records by a key and does not create a relationship. Use append for combining similar monthly files, merge for a query-level join, and a Data Model relationship for connecting separate model tables during analysis.
Rules for reliable keys and model design
Before making a relationship, check the key columns rather than assuming that similarly named fields are compatible. A relationship can be blocked or give misleading analysis when the lookup key is duplicated, the data types differ, or values have inconsistent formatting. For example, a text identifier containing leading zeros may not be equivalent to the same digits stored as a number.
- Use a stable identifier such as ProductID or CustomerID, not a name that can change or be shared by multiple records.
- Make sure the lookup table has only one row per key. Remove duplicates only when that is logically correct; do not discard legitimate records just to force a relationship.
- Standardize types and clean unwanted spaces or inconsistent key formats in Power Query before loading.
- For a many-to-many situation, consider a bridge table with unique keys that connects the two sides. Excel’s model relationships are not a direct substitute for a many-to-many relationship between two tables.
- If a match depends on multiple fields, create a consistent combined key in both queries or reshape the data so a single unique key can be used. Excel’s relationship dialog connects one column from each table, not a pair of columns as a composite key.
Do not assume that a relationship itself fixes data quality. It does not fill in missing keys, resolve conflicting product records, or decide which of several duplicate lookup rows is correct. Those decisions belong in the source data or query-preparation process.
Using relationships in PivotTables and measures
A common reason to build relationships is to create a PivotTable from multiple tables without first joining every column into one worksheet table. When inserting a PivotTable, choose the option to use this workbook’s Data Model if Excel presents it. The PivotTable field list can then show fields from the model tables. If fields from separate tables do not combine as expected, confirm that the tables are in the model and the relationship connects the intended columns.
Relationships also support calculations in the model, including measures created with Data Analysis Expressions (DAX). A measure can summarize a value in a transaction table while users filter the report by fields in a related lookup table. You do not need to write DAX to use basic PivotTable summaries, but measures become useful when reporting needs a reusable calculation or more control than a standard sum or count provides.
Common problems and fixes
- The relationship will not create: Check that both tables are in the Data Model, the selected columns have compatible data types, and the lookup-side column contains unique values.
- The button is unavailable or a table is missing: The table may be only on a worksheet and not in the model, or the workbook may not contain a Data Model yet. Review the query’s load settings.
- Some records do not appear in a report: Look for unmatched or blank keys, extra spaces, leading zeros, or a filter that excludes the records. A relationship does not make a missing lookup row appear automatically.
- Totals seem too large: Check for duplicated rows in the source tables, an incorrect key, or an unintended many-to-many pattern. A relationship can make a PivotTable combine data, but it cannot correct an inflated source total.
- A refresh breaks the model: A query may have renamed or removed a key column, changed its data type, or altered the table structure. Restore a stable key and update the relationship if the model’s columns have changed.
When troubleshooting, inspect a small sample of key values in both tables and verify that a lookup key appears once while its matching detail key can repeat. This simple check often reveals whether the issue is a model setting or a data-cleaning problem.
Refreshing data and platform differences
Power Query refreshes the data returned by its queries; a relationship is a separate part of the workbook’s Data Model. Use Data > Refresh All to update connections and queries. If a refresh preserves the table structure and key columns, the existing relationship will generally remain in place. If a query changes or removes a related column, check the relationship afterward and recreate it if necessary.
These instructions are for Excel for Microsoft 365 on Windows. Excel for Mac has Power Query features, but its Data Model and Power Pivot relationship-authoring capabilities differ from Windows and are not a like-for-like way to build and manage this model. Excel for the web also does not provide the full Windows Power Pivot model-authoring experience. If you need to create or troubleshoot workbook relationships, use a supported Excel desktop version on Windows and confirm that your Microsoft 365 edition includes the relevant Data Model features.
Frequently Asked Questions
Does Power Query create relationships automatically?
Not as part of transforming or loading a query. Power Query prepares and loads the tables; create or verify their relationships in the Excel Data Model. Excel may recognize some connections in certain workflows, but you should inspect the model rather than rely on automatic detection.
Can I create a relationship between two worksheet tables?
They need to be available in the Data Model for a model relationship. Loading a query result to a worksheet alone is not the same as adding it to the model. Use the query load options to include it in the Data Model, then create the relationship.
Can I relate tables using two columns?
The relationship connects one column from each table. If the true key is made of multiple fields, create a consistent combined key in both tables or restructure the data to provide a single unique identifier.
Should I merge queries or create a relationship?
Merge queries when you want Power Query to join columns into a new query result. Create a relationship when you want separate Data Model tables to work together in a PivotTable or model calculation. You can use both methods in one workbook for different outputs.
Will refreshing Power Query remove my relationships?
A normal refresh that preserves the relevant model tables and key columns should not remove the relationship. A change to a table or key column can invalidate it, so review relationships after changing query structure.
Related
- Excel Power Query for Data Cleaning: A Practical Guide
- Uni Budget Spreadsheet: Build a Student Budget in Excel
- Excel Power Query Editor: What It Does and How to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Indent Down in Excel: Cells, Text, and RowsIn Excel, indentation moves cell content horizontally, usually to the right; it does not move text down within…
- How to Copy an Image in Excel: Clear Steps and TipsTo copy an image in Excel for Microsoft 365 on Windows, click the image’s edge to select it,…
- Excel Go to Bottom of Data: Keyboard Shortcuts Explained ClearlyIn Excel for Microsoft 365 on Windows, press Ctrl+Down Arrow to jump to the bottom edge of the…
- Excel Cell Lock: How to Unlock or Remove ProtectionTo remove a cell lock in Excel, select the cell, press Ctrl+1, open the Protection tab, clear Locked,…
- How to Cancel a Mail Merge in Word Without SendingTo stop a mail merge in Word, press Esc or choose Cancel if a progress window offers that…
- Excel Shortcut to Delete a Row (Windows and Mac)In Excel for Microsoft 365 on Windows, select a cell in the row and press Shift+Space to select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.