Identify offsetting entries based on multiple criteria

Hi Ian_XYZ 

In this scenario, you have entries where customer P001 has negative values that should offset matching positive values belonging to customers whose IDs start with "T". The match needs to be based on the columns Location, Product, and Description, with the Value being the same magnitude but opposite sign. While you already achieved this using multiple merge operations in Power Query, the performance has degraded due to the number of joins. A more efficient approach is to add a helper column that creates a matching key and then use a single merge or a conditional check instead of multiple merges. For example, you can create a new column combining Location & Product & Description & ABS(Value) as a "match key", filter P001 rows, and check if a corresponding "T*" customer row exists with the same key but opposite sign. Then, create a Comments column that returns "Cleared" if the rule matches, otherwise blank. Alternatively, this logic can be computed in DAX after loading the table, using a calculated column to check for an offsetting T-customer row by searching the table with the same key and reversed value using functions like CALCULATE, FILTER, and COUNTROWS. The idea is to reduce join operations and instead rely on faster column operations and conditional lookups, which keeps the query refresh responsive even when working with large datasets.