Forum Discussion
How to use Power Query in creating a new spreadsheet
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.
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.
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.
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.