Thankyou for using Microsoft Q&A platform and thanks for posting your query here.
As per my understanding , you are trying to understand the concept of look up tranformation in mapping dataflow. Please let me know if that is not the ask here.
Lookup transformation is similar to a left outer join. All rows from the primary stream will exist in the output stream with additional columns from the lookup stream.
Using lookup transformation, you can find the matches between two datasets and once you get the matched subsets, you can fetch out the first and the last matches using ismatch()
function.
Complete demonstration can be seen in the following video: ADF Mapping Data Flows Lookup Transformation
Hope it helps. Kindly accept the answer if it's helpful or revert back with additional query if you have any. Thankyou