This is a classic sign that your spreadsheet has outgrown its original design. You have a perfectly reasonable need: apply a custom calculation to duplicate rows, leave unique rows alone, and have the entire system survive a Power Query refresh. Your frustration is valid, and the solution lies not in wrestling with conditional formulas, but in rethinking how you structure your logic.
Power Query can handle this, but only if you stop thinking like a traditional spreadsheet user. Spreadsheets reward manual checks and row-by-row edits. Power Query rewards a clear question: what defines a duplicate? You know a product is duplicated when it appears more than once. That condition, count per product greater than one, is your dynamic flag. In Power Query, you can calculate that count, then use a conditional column that applies your special formula when the count is above one and keeps the original total when it is not. This approach survives any refresh because Power Query re-evaluates the count against the fresh data every time you load.
What makes this tricky is that you are merging two tables. Duplicates might come from one source table or from the overlap between them. The count must happen on the merged result, not on either source individually. That is an easy fix in Power Query: after you merge, group by product, add an index, then merge the count back. The workflow is reliable, but it requires you to step away from Excel's grid logic and into Power Query's step-by-step transformation model.
The practical takeaway is that your request is not an edge case. It is a symptom of data that arrives from multiple sources and needs consistent handling after every merge. If you build your logic purely in Power Query, you get a solution that runs automatically on every refresh. If you rely on Excel formulas, you will find yourself manually adjusting ranges and rebuilding logic after every data change. Your time is better spent learning how to add a conditional column in Power Query than it is fighting with nested IF statements that break the moment new data arrives.