Log In
Register
Home
Active Member
June 21, 2021
Offline
Hi,
I'm trying to match the sum of the transaction amounts in query 1 in a sequential order against the bank receipt amounts in query 2 and get the end result table with the bank receipt date. Is there a way to easily do so in power query?
| Query 1 | Query 2 (Bank statement) | ||||
| Transaction date | Transaction amount | Bank receipt date | Bank receipt amount | Comments | |
| 1-May-21 | 10 | 7-May-21 | 30 | For 1 and 2 May transactions | |
| 2-May-21 | 20 | 20-May-21 | 60 | For 3, 4 and 5 May transactions | |
| 3-May-21 | 10 | ||||
| 4-May-21 | 20 | ||||
| 5-May-21 | 30 | ||||
| End result | |||||
| Transaction date | Transaction amount | Bank receipt date | |||
| 1-May-21 | 10 | 7-May-21 | |||
| 2-May-21 | 20 | 7-May-21 | |||
| 3-May-21 | 10 | 20-May-21 | |||
| 4-May-21 | 20 | 20-May-21 | |||
| 5-May-21 | 30 | 20-May-21 |
See Answer
July 16, 2010
Offline
Hi Kenneth,
Welcome to our forum!
Unless there is a unique identifier between the transactions and bank receipts, like a transaction ID/invoice number etc. then there's no way for Power Query to accurately match multiple amounts to a total amount received.
Mynda
Active Member
June 21, 2021
Offline
Hi Mynda,
Noted. I wanted to check on this as I just started using Power BI and I'm not sure if the above can be done.
I only managed to do this for individual bank receipts which tallies to the individual transaction amount and the amount is highly likely to be unique by merging based on the amount.
Thanks for clarifying on this.
Can I check if there are other tools which you know of which can do this? Maybe excel vba?
July 16, 2010
Offline
No worries.
Excel doesn't have any built in functionality for this. I'm sure someone could write some VBA to do it, but it wouldn't matter what tool you used to match multiple transactions to a single transaction amount, all will be risky when you have no other identifier to accurately match them.
Mynda
Answers Post
1 Guest(s)
