Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
audrianna21
Frequent Visitor

Dataflow Gen2 Lookup Activity - Get First Row Only

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 ProcessPrevious Run DateTime
Bob11/2/2024 0:01
Bob32/2/2024 0:01
Bob22/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?

1 ACCEPTED SOLUTION
v-junyant-msft
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
v-junyant-msft
Community Support
Community Support

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.

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?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors