Pivot Table Issues

Fix Excel Power Pivot Relationships for Client and Service-Date Identifiers

Steven ParkerSteven Parker Nov 18, 2026 869 views

Question details

The user needs payment amounts grouped by a client and service-date identifier in a Power Pivot model.

Fix Excel Power Pivot Relationships for Client and Service-Date Identifiers
Product
Microsoft Excel Power Pivot
Device & OS
not provided
Scenario
A PivotTable combines billing and insurance payment data linked by a client-name and service-date identifier.
Observed behavior
The paid amount is summarized across all clients instead of being grouped by identifier.
Before you start

In Power Pivot, identify the billing and payment tables, their client and service-date fields, and the paid-amount column. Check whether the proposed lookup key has duplicates and whether dates contain time values.

Solution 1Recommended

Create a Unique Client and Service-Date Lookup Table

Use a shared lookup table when billing and payment tables both contain repeated identifiers.

The lookup table must contain one row per client and service-date identifier. It filters both transaction tables through one-to-many relationships, so the PivotTable can group their values consistently.

1
Standardize the source fields

Open each table in Data > Get Data or Queries & Connections, then use Power Query to trim client names and convert service dates to Date. Apply the same name and date rules to both tables.

2
Create matching identifiers

In each Power Query table, use Add Column > Custom Column to combine the standardized client name and date into a text key. Use the same date format and a separator that cannot occur in the client names.

3
Build the lookup table

In Power Query, create a query containing the identifier columns from both tables using Home > Append Queries. Select the identifier column and choose Home > Remove Rows > Remove Duplicates, then load the query to the Data Model.

4
Create the relationships

Open Power Pivot > Manage > Diagram View. Create a relationship from the unique lookup identifier to the billing identifier and another from the lookup identifier to the payment identifier. Ensure each lookup column is on the one side and the matching columns have the same data type.

5
Build and refresh the PivotTable

Add the lookup table's identifier to Rows. Create a measure such as Total Paid := SUM('Payments'[Paid Amount]), using your actual table and column names, and add it to Values. Select Data > Refresh All and check totals for several identifiers.

Create a Unique Client and Service-Date Lookup Table
Check unmatched identifiers: If a payment still appears under a blank group or is missing from an expected group, compare its standardized client name and date with the lookup key and refresh the model.
Free Microsoft Office alternative

Work With Billing and Payment Spreadsheets in WPS Office

WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work. It does not fix Excel Power Pivot relationships; use Excel to repair the Data Model.

Review and organize billing and payment spreadsheetsOpen and edit common Microsoft Excel formats, including XLSXUse a familiar spreadsheet interface for everyday tasksMove routine spreadsheet work to a free, lightweight Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the same payment total appear for every client?

The PivotTable row field may not be filtering the payment table. In Power Pivot > Manage > Diagram View, check the relationship path and use the shared lookup table's identifier in Rows.

Can both source tables contain duplicate identifiers?

Yes. Billing and payment tables can each contain multiple rows per identifier, but the shared lookup table must have one row per identifier for one-to-many relationships.

How do I handle service dates that include times?

In Power Query, select the service-date column and convert it to the Date type before creating the identifier. Apply the same conversion to both source tables.

What should I check if a relationship cannot be created?

Check that the lookup identifier has no duplicates and that both relationship columns use the same data type. Also inspect blank keys, extra spaces, inconsistent names, and date formatting.