Pivot Table Issues

How to Fix Repeated Row Items in an Excel Power Pivot Relationship

Kushani NimanthikaKushani Nimanthika Nov 13, 2026 869 views

Question details

The user needs to stop a Power Pivot PivotTable from returning every combination of values from two related categorical tables.

How to Fix Repeated Row Items in an Excel Power Pivot Relationship
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
The user built a Power Pivot data model with two related categorical tables and placed fields from both tables into a PivotTable.
Observed behavior
The PivotTable repeats all combinations of row items instead of respecting the defined relationship.
Before you start

Before modifying the data model, open the Power Pivot window and note the current relationship columns, direction, and table names. Also refresh the PivotTable once to rule out a simple cache issue.

Solution 1Recommended

Check and Correct Relationship Direction and Key Columns

Use this first when related tables produce a cross-join style repetition in the PivotTable.

Power Pivot relationships require a lookup table on the one side and a related table on the many side. If the direction is reversed or the wrong key columns are selected, the PivotTable cannot filter related rows correctly.

1
Open Relationship Manager

In Excel, go to Power Pivot > Design > Relationships to open the relationship dialog.

2
Inspect the active relationship

Select the relationship used by the PivotTable and note the Table, Related Lookup Table, and Column entries.

3
Set the correct one-to-many direction

Make sure the unique lookup or category table is on the one side and the transactional or detail table is on the many side. If necessary, delete and re-create the relationship with the correct direction.

4
Save and refresh

Click OK to save, then go to PivotTable Analyze > Refresh and confirm the row combinations no longer repeat.

Check and Correct Relationship Direction and Key Columns
Expected result: The PivotTable should now show only valid combinations that come from the many-side table filtered by the lookup table.
Free Microsoft Office alternative

Analyze PivotTables in WPS Spreadsheets Without Power Pivot

WPS Office does not include the Power Pivot add-in, but WPS Spreadsheets is a free, lightweight alternative that opens Excel workbooks and provides familiar PivotTable tools for summarizing, filtering, and refreshing data with Microsoft Office format compatibility.

Free PivotTable and spreadsheet analysis without the Power Pivot add-inOpens and saves .xlsx files, preserving Microsoft Excel format compatibilityFamiliar Ribbon interface for a seamless migration from ExcelLightweight office suite that handles PivotTable reports and daily spreadsheet tasks
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Power Pivot PivotTable show every combination of two category fields?

This usually means the relationship is missing, reversed, or the lookup table key column contains duplicates. Open Power Pivot > Design > Relationships and verify that the one-side table is a unique dimension table connected to the many-side table.

How do I check relationship direction in Excel Power Pivot?

In the Power Pivot window, select Design > Relationships. In the dialog, check the Table and Related Lookup Table columns. The unique lookup or category table must be on the one side.

Can WPS Office open a workbook that uses Power Pivot relationships?

WPS Office can open .xlsx files, but Power Pivot data models are not available in WPS Spreadsheets. You can still use standard PivotTables from worksheet tables or ranges for common analysis.

What should I do if the lookup table has duplicate keys?

Remove duplicate rows from the lookup table, or build a unique dimension table with one row per key. Then re-create the relationship in Power Pivot and refresh the PivotTable.

Does WPS Spreadsheets support PivotTable refresh from Excel data?

Yes. WPS Spreadsheets supports PivotTables from worksheet ranges and can refresh data changes. However, it does not support the Power Pivot data model add-in.