Conditional Merge Query across two tables

[ChrisDiener]

I have an On-Hire table that lists all transactions or instances where my customers hire equipment.

e.g. Lawn Mower was hired by Customer 1234 on January 18, 2024 and agrees to return the item on 19 January 2024.

I have another table titled Off-Hire that lists all transactions or instances where customers return equipment.

e.g. Lawn Mower was returned by Customer 1234 on 20 January, 2024

I am wanting to return a column on the On-Hire table to state if the item has been returned (Yes/No).

The problem is that the only links between both tables that can be used are "Customer Name" and "Hire Item". And over time, this customer will re-hire the same piece of equipment. A simple merge query will fail because of multiple Off-Hire transactions for the same customer and piece of equipment, and as you can see above in bold, the return date does not match the agreed return date on the On-Hire transaction.

Is there a way to conditionally search or filter the Off-Hire transaction table to find the most current Off-Hire transaction for that "Customer Name" and "Hire Item"? Fortunatly the way I have built the form which collects the data for this table/report only allows the Hire

[Anonymous · Accepted solution]

Hi @ChrisDiener ，

It is assumed that if an object is borrowed, it must be returned.
Try grouping the two tables by [Customer Name] and [Hire Item] and adding an Index column to mark a loan/return.
On-Hire Requests

Off-Hire Requests

Merge:

Expand and filter rows:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[ChrisDiener]

I forgot to mention, and I am unsure if it helps.

The Off-Hire form which I developed only allows the customer to select a "Hire Item" which is currently allocated to them. Therfore, the date which that Off-Hire notification is submitted is (at that time) true.

The problem still remains where a customer has multiple instances where the same item has been hired multiple times. How can I define what hire is still outstanding or complete... Yes/No.