Power Query Eliminate Contra entries

[NSCJPN]

Inspired by Mr Fluff‘s quick response to my query on Sorting Sheets of a workbook, I am seeking another help.In accounts, you have ‘Contra entries’ - same amount in Debit and Credit column of a ledger. Is it possible to eliminate contra entries of a ledger using Power Query or VBA.?

 

[GraH]

I do not see a reason why not.
Conditions could be for a PQ solution:
- having the customer account ref in both of a payment ref or invoice ref,...
- matching ? amounts
- merge on both columns, full outer join to allow having left anti, matching and right anti records. In first not second table, in both, not in first but in second table.

 

[NSCJPN]

Thanks
contra entries are in the same table.Thinking of splitting the query into two and then follow yr steps.Ref is missing in some instances; the contra amount is also not on the same date and hence will have to rely on amount column only.

 

[GraH]

You can do a join on the same table, using merge as new, or copy your query and then do the join.
You may want to try with a simple pivot since data is in one table. Possible when you have a positive and negative amounts value. Difficult to give clear advice without seeing data.