Fix Excel Power Pivot Relationships for Client and Service-Date Identifiers
Question details
The user needs payment amounts grouped by a client and service-date identifier in a Power Pivot model.

- 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.
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.
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.
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.
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.
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.
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.
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.

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.

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.




