Dataflow Gen2 Lookup Activity - Get First Row Only

[audrianna21]

I am currently attempting to get 1 record out of a delta lake table in my lakehouse. When configuring the lookup activity there is a selection to First Row Only. I do want only 1 row, but I need a way to filter down the data in the table to get to the right record.

For example this is my table:

Running Process	Previous Run DateTime
Bob1	1/2/2024 0:01
Bob3	2/2/2024 0:01
Bob2	2/14/2024 0:01

In the lookup activity I want to basically filter where Running Process = Bob3 and then only take the first row it gets, but I can't figure out how to do this. It looks like I can add dynamic content to the First row only checkbox. 

Has any been successful doing what I'm talking about?

[v-junyant-msft · Accepted solution]

Hi @audrianna21 ,

To filter the data where "Running Process = Bob3", you will need to use a query within the Lookup Activity. In the settings for the Lookup Activity, specify your query in the 'Query' field to filter the data. For example:

SELECT * FROM your_table WHERE RunningProcess = 'Bob3'

Replace your_table with the actual name of your table. This will ensure that the Lookup Activity only retrieves records where the Running Process is equal to 'Bob3'.

Selecting the First Row: Once you have filtered the data, the "First row only" checkbox will ensure that only the first row of the filtered result set is returned. Make sure this checkbox is selected in the Lookup Activity settings.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[audrianna21]

Hi @v-junyant-msft,

To ensure I'm looking at this correctly, I can only see the Use Query option if I'm using a Data store type of External and then connect to my delta table in my lakehouse through the SQL endpoint, yes?

Right now I can't use a query if I select a Data store type as Workspace and then Workspace data store type Lakehouse?