Filter rows from an excel based on another lookup activity in Data Factory

Lilac 80 Reputation points


I have 2 lookup activities in my pipeline. The first Lookup is querying a table and outputs a list of tables in the below format.

	"count": 3,
	"value": [
	"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (Mumbai)",
	"billingReference": {
		"activityType": "PipelineActivity",
		"billableDuration": [
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
	"durationInQueue": {
		"integrationRuntimeQueue": 0

The other lookup fetches an excel which has multiple columns, one of which is SOURCE_TABLE

My objective is to filter out the rows from the excel where SOURCE_TABLE matches with the values of the first Lookup. For this I am iterating over a ForEach activity as below.


Inside it I have a filter activity, that has item as `@activity('lookup_excel').output.value`

and condition as @equals(item().SOURCE_TABLE,activity('lookup01').output.value)

The filter activity is not able to filter the rows although the pipeline is running fine. Any help is hugely appreciated.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
7,983 questions
0 comments No comments
{count} votes

Accepted answer
  1. ShaikMaheer-MSFT 34,536 Reputation points Microsoft Employee

    Hi Lilac,

    Thank you for posting query in Microsoft Q&A Platform. Glad to know that your issue resolved. I am writing your resolution details here (as user cannot accept their own answer).

    I resolved it by taking a different approach. I separated the 2 lookup activities. The lookup activity for the excel file is part of a child pipeline inside a forEach activity.

    Please consider mark it as accepted answer. Accepted answers help community as well. Thank you.,

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Suba Balaji 8,386 Reputation points

    Hi Lilac

    I think you have used the wrong look up activity output in the equals expression in the filter activity

    @equals(item().SOURCE_TABLE,activity('lookup01').output.value) Here item().SOURCE_TABLE is the first look up record, which is table_name. activity('lookup01').output.value) : this refers to the first lookup again.

    Nowhere we are doing comparison against second lookup as far as i understand.

    Inside foreach loop, for the filter activity, Instead of the below expression


    Please try this


    Let us know how it goes