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

Lilac 80 Reputation points
2023-08-31T17:21:29.4266667+00:00

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.

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

Accepted answer
  1. ShaikMaheer-MSFT 38,521 Reputation points Microsoft Employee
    2023-09-04T09:50:45.68+00:00

    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. Subashri Vasudevan 11,226 Reputation points
    2023-09-01T02:08:10.99+00:00

    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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.