Hi @ALLLL ,
Thankyou for choosing Microsoft Fabric Community.
Thanks for the detailed explanation. This looks more like a reconciliation scenario than a simple lookup, especially since one payment can cover multiple invoices, one invoice can have multiple payments, and the matching is based mainly on amount and date rather than a unique identifier.
A few additional details would help clarify the best approach. Do the bank transactions contain any invoice number or customer reference that could help link them to the ERP data? Is there an acceptable date tolerance between invoice date and payment date? Also, are small amount differences common due to rounding or bank fees? Finally, would it be acceptable to automate the majority of matches and manually review only the unmatched or ambiguous cases?
The reason I ask is that Power Query merges work best when there is a clear matching key. In your case, this behaves more like a many-to-many reconciliation problem, so understanding the business rules and data structure will help determine the most reliable solution.
For more information go through the documentation:
https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query
Best Regards,
Abdul Rafi
