Why Did My Excel Merge Double the Total?
Your orders add up to $1,000. You merge them with a price history export in Excel Power Query, expand the new column, and the total jumps to $2,000. Before you check the SUM formula, count how many times each order line appears.
In a left outer merge, Power Query keeps every order line and finds all matching rows in the other table. When you expand those matches, an order line with three matches appears three times. An order line with zero matches stays in the output, with blank values from the other table.
Practice with four orders in Excel
The downloadable example below uses customer terms. It follows the same merge logic as the price-history example above.
Download the practice workbook (.xlsx)
The practice workbook contains the Orders, Customer Terms, and Order Lines tables as worksheet ranges. Load the ranges into Power Query to reproduce the merge.
| Check | Result | Source |
|---|---|---|
| Before expanding | 4 orders, $550 | Orders!A2:C5 |
| Lookup key with two matches | C-02, one current and one retired row | Customer Terms!A3:C4 |
| Orders repeated by those matches | O-102 ($200) and O-104 ($75) | Orders!A3:C3 and Orders!A5:C5 |
| After expanding both matches | 6 rows, $825 | $275 counted twice across the two C-02 orders. |
| After confirming one current row per customer | 4 orders, $550 | Customer Terms!A3:C3 contains the current C-02 record. |
Try this question. Join Orders to Customer Terms by Customer ID and count the rows before and after expansion. Which key inflated the order total, by how much, and which exact Excel rows explain it? Then explain why joining to Order Lines can legitimately return six rows.
Open Jolene.pro to run the example · Browse more Jolene.pro Workbook Studies
To reproduce it, load Orders and Customer Terms into Power Query using Data → From Table/Range. Merge on Customer ID with a Left Outer join, then expand Terms. Group the lookup by Customer ID and count records to find C-02. For this report, confirm only one row per customer is marked Current before using that filter on refresh.
Try the other join in the workbook as well. Orders to Order Lines legitimately expands to six rows. Sum the line amounts to keep the $550 total, or group the lines to order level before joining. Summing the order header after expansion repeats the two split orders.
Microsoft’s guide to merging queries in Power Query.
1. Save the totals before you merge
Record the row count and the sum of the amount column in your original orders table. Keep a unique ID for each source row. If an order can have several lines, use the order number and line number together, or add an index column before the merge. You need to tell a repeated source line from two legitimate lines on the same order.
For this example, paste each CSV into its own Excel sheet and turn each range into a table. The first is Orders.
OrderLineID,SKU,OrderDate,OrderAmount
O-101,A-100,2026-09-18,500
O-102,B-200,2026-09-18,300
O-103,C-300,2026-09-18,200
The second is Price History. An empty end date means the price is still active. Set both date columns and the order date to the Date type in Power Query.
SKU,PriceRowID,StartDate,EndDate,UnitPrice
A-100,P-001,2026-01-01,2026-03-31,18
A-100,P-002,2026-04-01,2026-08-31,20
A-100,P-003,2026-09-01,,22
B-200,P-004,2026-09-01,,15
C-300,P-005,2026-09-01,,12
2. Find the order line that multiplied
Load the two tables into Power Query. Open Merge Queries, put Orders on the left, select SKU in both tables, and choose Left outer. Expand PriceRowID from the new column so you can see which price record produced each output row.
The merged table has five rows. O-101 appears once for each of P-001, P-002, and P-003. Its $500 amount gets counted three times. The other order lines still appear once each. The two extra copies of O-101 explain the entire $1,000 increase.
For a larger export, make a copy of the merged query for your audit. Use Group By on the original line ID with Count Rows, then filter to counts above one. Go back to the expanded results for those IDs and inspect their matched price row IDs. You can also group the price table by SKU before merging to see which SKUs have multiple records.
3. Fix the match and check the totals again
All three orders in this example are dated September 18. Filter Price History to rows with a start date on or before September 18 and an end date on or after September 18 or blank. Then merge again. A-100 now matches only P-003. The result is back to three rows and $1,000.
If your orders span multiple dates, check the price window against each order’s own date. Keep the original line ID and flag lines with no eligible price or more than one eligible price before you use the result. Overlapping price windows need a business rule or a source correction. Keeping the first row for each SKU can attach the wrong price.
After the fix, compare the row count, the amount total, and the count per original line ID. A matching total can hide a dropped line and an extra copy elsewhere.
What to ask Jolene
If you already have the exports, put the original orders and expanded merge on separate tabs, then add the price history. Keep the IDs and amount column in the file so you can trace the result back to each row.
Which order lines were duplicated in this merge? Show the matched price row IDs and how much each line added to the total.
Try it with your own workbook at Jolene.pro. Follow Jolene.pro on LinkedIn for more spreadsheet guides.
Microsoft references for merging queries, left outer joins, and grouping rows.
This example uses synthetic data. Check your source records and pricing rules before using the merged output in a report.
If repeated rows came from overlapping exports before the merge, try the duplicate-record guide and CSV practice workbook. It separates matching copies from conflicting versions, including the same transaction ID in different accounts.
1 comment