Power Pivot: Merge Two Tables or Create a Relationship?

Power Pivot does not have a Power Query-style Merge command: to combine tables into one query, use Data > Get Data and merge them in Power Query, then load the result to the Data Model. If you want to analyze two tables together while keeping them separate, add both to the Data Model and create a relationship in Power Pivot instead.

What “merge” means in Excel

People often use “merge” to describe two different jobs. One is joining columns from one table onto another table, producing a wider table. The other is connecting separate tables so a PivotTable can analyze them together. Excel handles these jobs in different places.

Power Query is Excel’s data-import and transformation tool. Its Merge command joins tables based on matching values in one or more columns. You can expand selected columns from the second table and load the resulting query to a worksheet, the Data Model, or both.

Power Pivot is the tool for working with the Excel Data Model: a collection of tables and relationships used for analysis. It lets you build PivotTables from related tables without first flattening every field into one large table. So the short answer depends on your goal: use Power Query to make one joined table; use a Power Pivot relationship to analyze separate tables together.

Merge tables with Power Query

In Excel for Microsoft 365 on Windows, a typical workflow is to import or turn each source range into a query, merge the queries, and load the outcome. If the data is already formatted as Excel tables, give each table a clear name first using Table Design > Table Name. This makes the query list easier to understand.

Select a cell in the first table and choose Data > From Table/Range. If the source is elsewhere, use the relevant option under Data > Get Data, such as a workbook or text file.
In Power Query Editor, check the column names and data types. Repeat the import for the second source if it is not already listed as a query.
Select the query that will be the primary, or left, table. Choose Home > Merge Queries to change it, or Home > Merge Queries as New to create a separate result query.
In the merge window, choose the other query. Select the matching column in each preview. For a multi-column match, hold Ctrl while selecting corresponding columns in the same order in both previews.
Choose a join kind, select OK, then use the expand button on the new column to choose which fields to bring in. Uncheck the option to prefix expanded column names if you do not want the source query name added.
Choose Home > Close & Load To… and select the destination. To use the result in Power Pivot, choose Only Create Connection and check Add this data to the Data Model.

Exact import options can vary with the source and Excel build, but the key operation is in Power Query Editor, not the Power Pivot window. Power Query stores the transformation steps, so refreshing the query can repeat the merge when the source data changes.

Choose the right join kind

The join kind determines which rows survive the merge. In the merge dialog, the first table is the left table and the second is the right table. The choice matters: a join that drops unmatched rows can make totals appear incomplete.

Left Outer keeps every row from the first table and adds matching rows from the second. This is a common choice when enriching a main list with descriptions or categories.
Inner keeps only rows with a match in both tables. It is useful when the analysis should include only records represented on both sides.
Full Outer keeps rows from both tables, including unmatched ones. Null values in expanded fields indicate that a match was not found.
Right Outer keeps all rows from the second table and matches from the first. It is similar to reversing the table order and using Left Outer.
Left Anti returns rows from the first table with no match in the second. This is useful for finding missing lookup entries or records that need investigation.

For example, a sales table may contain an ItemID and a separate product table may contain ItemID, product name, and category. A Left Outer merge keeps every sale and adds the product details where the IDs match. If an ID is absent from the product list, the sale remains but its expanded product fields are blank.

Use a relationship instead of flattening

If the goal is a PivotTable that shows sales by product category, a relationship is often cleaner than merging the product name and category into every sales row. Add the sales and product tables to the Data Model, then connect their shared ItemID fields. The product table should have one row per ItemID; the sales table can have many rows for the same ID.

In Power Pivot, choose Manage to open the model. In Diagram View, create a relationship by connecting the matching key fields, or use the relationship controls in the Power Pivot window to specify the two tables and columns. The unique product IDs belong on the one side, and repeated sales IDs on the many side. You can then create a PivotTable from the Data Model and use fields from both tables.

A relationship does not physically copy the product fields into the sales table. Instead, it tells the model how to associate rows when a PivotTable groups or filters data. This structure is often called a star schema when a central fact table, such as sales, connects to descriptive dimension tables, such as products, dates, or customers. It can reduce duplicated data and make the model easier to maintain.

How to decide which method fits

Choose a Power Query merge when you need a single shaped output, want to remove or rename fields, need to filter data during preparation, or must deliver a joined table to someone who will not use the Data Model. A merge is also useful when the source structure itself needs transformation before analysis.

Choose a relationship when the tables have a clear key, describe different entities, and you want to use their fields together in Data Model PivotTables. Keeping transactions separate from product, customer, or calendar details avoids repeating the same descriptive values across many transaction rows. It also helps when several fact tables share the same dimension, such as actual sales and budget figures both using a calendar table.

These choices are not mutually exclusive. You can clean and reshape each source with Power Query, load the resulting tables to the Data Model, and then create relationships between them. You can also merge some sources and relate the finished query to other model tables. Select the design that makes the data’s meaning and maintenance requirements clearest.

Keys, duplicates, and data grain

The matching field is called a key. It should represent the same thing in both tables. An order number should match an order number, for example, not a customer number that happens to contain similar-looking values. Before merging or relating tables, check that key values are populated, consistently formatted, and stored with compatible data types.

For a one-to-many relationship, the column on the one side must identify each row uniquely. If a product table contains two rows for the same ItemID, Excel cannot treat that field as a unique lookup key for a standard one-to-many relationship. A merge may still run, but duplicate matches can multiply rows: one sales record matching two product records can produce two output rows.

Also check the grain, meaning what one row represents. A sales table might have one row per order line, while a target table might have one row per month and region. Joining those tables only on month can repeat each target across many order lines and inflate totals. A relationship does not automatically fix a mismatched grain. Determine what each table records and whether the key fields define a valid connection before trusting summaries.

Common problems and practical checks

A merge that returns blanks for fields expected to match usually points to differences in the keys. Values can look identical while one is text and the other a number; IDs may have leading or trailing spaces; or one source may include leading zeros. In Power Query, inspect the data types and use appropriate cleaning or type-conversion steps before merging. Avoid converting identifiers to numbers if leading zeros are meaningful.

If row counts unexpectedly increase, inspect the second table for duplicate key values and confirm that the selected columns are the intended match. If rows disappear, confirm the join kind and check whether those key values exist on both sides. For a relationship that will not create, verify that the proposed one-side column is unique and that both columns use compatible data types.

After a successful merge or relationship, validate the output rather than assuming it is correct. Compare row counts before and after, inspect a few known IDs, look for nulls in important fields, and compare a total that should remain unchanged. In a model, check whether a PivotTable total changes unexpectedly when you add a field from another table. Such changes can reveal duplicates, an incorrect relationship, or a grain mismatch.

Excel availability and refresh behavior

These steps are written for Excel for Microsoft 365 on Windows. The Power Pivot add-in is a Windows Excel feature and may need to be enabled through File > Options > Add-ins; select COM Add-ins in the Manage list, choose Go, and enable Microsoft Power Pivot for Excel if it is available in your edition. Availability can depend on the Excel edition and organization’s installation.

Excel for the web does not provide the full Power Pivot authoring and management experience. Excel for Mac also does not include the Windows Power Pivot add-in, and Power Query capabilities differ by platform and build. If you need to build or edit a Power Pivot Data Model, use a supported Windows desktop edition of Excel. Refreshing a query reruns its saved import and transformation steps; it does not correct a bad key or an unsuitable join automatically.

Frequently Asked Questions

Can I merge tables directly in Power Pivot?

No. Use Power Query’s Merge Queries command to join columns into a query. In Power Pivot, create a relationship when you want separate Data Model tables to work together in PivotTables.

Is a Power Query merge the same as a relationship?

No. A merge produces a query with fields brought together according to a join kind. A relationship keeps tables separate and defines how their matching keys connect during Data Model analysis.

Should I merge or append tables?

Merge matches rows by key and adds columns. Append stacks rows from similar tables, such as monthly files with the same column structure. In Power Query, append is a separate command from merge.

Can I create a relationship if both tables have repeated IDs?

Not as a standard one-to-many relationship using a repeated ID on the one side. Find a genuinely unique lookup table or redesign the model so that the relationship has a valid unique side; do not remove duplicates unless doing so preserves the data’s meaning.

Will a merged query update when my source changes?

It can update when you refresh the query, provided the source remains accessible and the saved steps still apply. Refreshing repeats the transformation; it does not guarantee that new or changed source values will match correctly.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.