How to check if the value of the column is in array list- Azure data factory?

I wanted to check the customer Ids which are in the parameters.

This is my sample data.

Customer ID,Customer Name,Contact Person,Phone Number,Email Address

1001,ABC Inc.,John Smith,555-1234,john@abc.com

1002,XYZ Corp.,Jane Doe,555-5678,jane@xyz.com

1003,Acme Co.,Bob Johnson,555-2468,bob@acme.com

1004,Smith & Sons,Mary Smith,555-3698,mary@smith.com

1005,Jones Inc.,Tom Jones,555-7890,tom@jones.com

I have an array list and it is passed as a parameter to data flow. I added this as a new column along with the above data. After adding my data looks like this.

Customer ID,Customer Name,Contact Person,Phone Number,Email Address, Customer_profit_array

1001,ABC Inc.,John Smith,555-1234,john@abc.com,[1001,1002,1007]

1002,XYZ Corp.,Jane Doe,555-5678,jane@xyz.com,[1001,1002,1007]

1003,Acme Co.,Bob Johnson,555-2468,bob@acme.com,[1001,1002,1007]

1004,Smith & Sons,Mary Smith,555-3698,mary@smith.com,[1001,1002,1007]

1005,Jones Inc.,Tom Jones,555-7890,tom@jones.com,[1001,1002,1007]

Now, I want to display yes whenever customer id is available in that array. In the above example row1 and row2 should have yes and others should have value as no.

I tried giving expression for that column as iif({Customer ID} in $Customer_profit_array, 'Yes','No'). But this is not a valid expression. How to solve this?

To check if the data is in array, you can use contain function in dataflow.

Syntax:
contains(<value1> : array, <value2> : unaryfunction)

The result of contains function will be of Boolean type.

• Give the expression for new column as, iif(contains({ Customer_profit_array},#item=={Customer ID}),'Yes','No').

This expression checks if customer id is in Customer_profit_array column.

Output

Reference: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expressions-usage#contains