How to Fix Repeated Row Items in an Excel Power Pivot Relationship
Question details
The user needs to stop a Power Pivot PivotTable from returning every combination of values from two related categorical tables.

- 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 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.
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.
In Excel, go to Power Pivot > Design > Relationships to open the relationship dialog.
Select the relationship used by the PivotTable and note the Table, Related Lookup Table, and Column entries.
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.
Click OK to save, then go to PivotTable Analyze > Refresh and confirm the row combinations no longer repeat.

Remove Duplicate Keys from the Lookup Table
Use this when the relationship exists but repeated combinations continue because the one-side key column is not unique.
Validate Cardinality and PivotTable Field Layout
Use this if the relationship is correct but the PivotTable still shows all combinations because of field placement or cardinality settings.
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.

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.




