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 |
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
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?
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
Hi Mynda, noted. Thanks for your replies and comments on this!
