How to use Power Query in creating a new spreadsheet

[dolsvoid]

I have a spreadsheet that is generated by the accounting software and it lists all the transactions performed on the account. This includes charges and refunds. So for example on March 1st, I might have a charge for $99.99 and March 9th I would have a refund $99.99 listed in the spreadsheet. Here is an example of the spreadsheet:

What I want to do is run Power Query to run through this and generate a spreadsheet that only lists transactions that don't have an associated refund. As you can see I had a charge for $466.99 and then a refund for $466.99 so in the new generated spreadsheet I want these two transactions removed.

How can I do this with Power Query?

[#2]

@dolsvoid 

May I challenge your basic premise? I do this as a person who at one point had responsibilities for a major corporate HR and payroll database. Part of any database of that type is the recording of the history of transactions. Sometimes, a transaction gets reversed. Sometimes a transaction gets partly reversed. We kept the entire history. And we made sure that the "current balance" -- be it current pay, current position, current whatever -- was an accurate reflection of the whole history.

Maybe that's what you're asking for, although it sounds like you're asking to have the two transactions both removed since they cancel each other out. But they both happened, and presumably (certainly on some occasions) the refund could take place a day--or week, or month(s)--later than the original charge. On occasion, the refund could be partial, such that removing the two would not even make sense.

[We just got confirmation from one of our credit card companies that a charge that was incurred back in December has been credited back to our account, but that credit (refund) was in February. Those two transactions appear on the credit card statements of two different months; I want to keep them in my Excel tracking sheet with the dates in those two months, so that the running balances for the full history make sense.]

So I'm suggesting that you're better off keeping the entire history, just making sure that the final balance has accounted for the original charge when it happened, the refund (whether complete or partial) and the date when it happened.

[#3]

The report generated is one that is missing invoices, I want to narrow it down to actual transactions that have hit the account and not been returned so I can print and attach the proper invoices to each. I do not need to provide an invoice copy to transactions that have been purchased and refunded.

[#4]

@dolsvoid 

Your newer post makes clear that you're writing as a business person creating invoices--I was writing from the perspective of the consumer keeping track of charges incurred and the occasional refund, partial or full.

I'm still a believer in keeping the full history, for all kinds of reasons. And, for that matter, should not the invoice show BOTH the fact of original charge AND the fact of the refund? As your customer, I would find that full accounting reassuring. I'm not a CPA, but would want to check on accepted procedures in this kind of situation.

[#5]

@mathetes

first, I am generating a report of purchases I have made and printing out the invoices to add to my own records.

second, this is for my own internal records not clients.

third, my question was regarding how to use Power Query to generate a very specific report that would help me filter down some data to make my work a bit easier not a request for some accounting lesson based on some delusional circumstances that aren't even application.

If you want to actually help then I would be grateful but refrain from giving ill suited opinions.

[#6]

@dolsvoid 

Thank you for finally explaining the context fully. A lot of my confusion could have been avoided had the original picture been clearer. And, for the record, in my own personal records--as I referred to them--I keep the full history.

I don't think you need Power Query in your situation. Just use FILTER. That link will take you to a website with instructions on how the function works. It can be used to filter out, or filter in, whatever records meet your designated criteria.

And here's a good video that Microsoft used to introduce the FILTER function.