How to combine two record into one

Hi SimonLu, to achieve the desired result, you need to be sure that the table is sorted correctly. So you have each "install and remove" pair one under another. This is required because you might have multiple activities on the same equipment at the same location many times, and to achieve the desired result, we'd need to temporarily split Intall and Remove into separate tables. Now some details 🙂

Let's load the initial table, add a key column (you'll see why later), and sort it by the key and equipment move date:

Now, let's get only a list of installations. doing so, we get the necessary number of rows for the final table. We also assume that removal can't take place if there was no previous installation, which seeems to be logical. We can do so by referencing the main table. By doing we can now keep all columns related to the instalaltion.

Let's prepare the table with removals only in a similar way and keep only removal relevant information:

The final task is to merge them in a right way. To do so, we'll use a key, so the correspoding removal on equipment will be correctly assinged to the installations. However, the issue arises when you have multiple activities over the same equipment at the same location, which will cause rows duplication.

The following transformations of "Final table" are aimed to remove wrong rows to obtain the requested result:

This approach would work well if your database contain up to million records. Otherwise, try to map pairs in the source system or make sure that the key is added and the table is sorted in the database (rather than in Power Query).

See pbix attached for details and good luck with your project!