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

Question

Hi,

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": [
		{
			"TABLE_NAME": "CUSTOMERS"
		},
		{
			"TABLE_NAME": "SALES"
		},
		{
			"TABLE_NAME": "PITCH"
		}
	],
	"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.

@activity('lookup01').output.value

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.

Answer 1

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.,

Answer 2

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

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

Please try this

@contains(@activity('lookup_excel').output.value,item().SOURCE_TABLE)

Let us know how it goes

Thanks