Power Query Problems

How to Prevent Power Query from Counting Payment Amounts Twice in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs a method to stop Power Query from duplicating applied payment amounts when multiple line items share the same account or name, ensuring the amount is only counted once while preserving all records.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Summarizing financial or payment data in Power Query where multiple line items correspond to a single payment, causing the payment amount to be duplicated across rows.
Observed behavior: Power Query duplicates the applied amounts on every row that shares the same account or name, leading to inflated totals when summarized.

Before you start

Before modifying your queries, ensure your source dataset includes a 'Date' or 'Service Date' column so you can accurately identify and sort the earliest record for each account.

Solution 1Recommended

Assign Payment Amounts to the Earliest Record using Power Query

Group and sort your data in Power Query to isolate the first applicable record, then use a conditional column to apply the payment amount only once.

By grouping your data by account and sorting by date, you can create a custom index for each account's transactions. This allows you to identify the very first transaction and apply the payment amount exclusively to it, preventing double-counting when you load the data into a PivotTable.

Sort by Account and Date

In the Power Query Editor, sort your 'Account Name' column in ascending order, then sort the 'Service Date' column in ascending order to ensure the earliest dates appear first.

Group the Data

Select the 'Account Name' column, click 'Group By' on the Transform tab, and choose 'All Rows' as the operation to create a nested table for each account.

Add an Index Column

Add a Custom Column to iterate through the grouped tables. Use the formula 'Table.AddIndexColumn([Count], "Index", 1, 1)' to append an Index column starting from 1 for each nested table.

Expand the Grouped Column

Click the expand icon on the nested table column to restore your original rows, making sure to select the newly created Index column.

Create a Conditional Column

Navigate to 'Add Column' > 'Conditional Column'. Set the rule: if 'Index' equals 1, output the 'Payment Amount'; otherwise, output 0 or null. You can then use this new column for accurate PivotTable summaries.

Data Aggregation: Once the query is loaded, you can safely use Power Pivot, a standard PivotTable, or a PivotChart to summarize the corrected amounts without worrying about duplicates.

Free Microsoft Office alternative

Looking for a Lighter Alternative to Handle Complex Excel Data?

While Power Query is a specific Microsoft Excel feature, you can easily manage, clean, and summarize your payment data using WPS Office. Enjoy a free, lightweight, and highly compatible suite that offers powerful built-in functions to handle duplicate data effortlessly.

1. Open Your Data in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the duplicate payment records.
2. Apply a Conditional Formula: Create a new column and use a formula like '=IF(COUNTIF($A$2:A2, A2)=1, B2, 0)' to display the payment amount only on the first row of each account.
3. Summarize with a PivotTable: Select your updated dataset, go to the Insert tab, and choose PivotTable to aggregate the corrected amounts accurately without complex queries.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsFree to use with a highly familiar, user-friendly interfaceLightweight application that runs smoothly on almost all devicesBuilt-in advanced PivotTable and conditional formula features to prevent duplicate counting

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query duplicate my applied amounts?

When multiple payment rows share the same account or name, merging tables or flattening data in Power Query can repeat the payment value for every matching line item. If left uncorrected, summing this column will result in inflated totals.

How do I only assign amounts to the earliest service date?

You must first sort your dataset by account name and service date, group the rows into nested tables, add a custom index starting at 1 for each group, and finally use a conditional column to apply the amount only when the index is 1.

Can I summarize the corrected data without VBA or complex formulas?

Yes. Once you have isolated the payment amount to a single row per account using Power Query, you can load the final table directly into a standard Excel PivotTable, Power Pivot, or PivotChart to summarize the results automatically.

Excel & Data Problems