Azure Data Factory - replace expression in derived column transformation using Mapping Data Flow

Question

I am trying to use Azure Data Factory for a Derived column transformation task one of my tasks is mentioned below,

DESCRIPTION_TEXT : UNILEVER GROUP ##### GBR

Remove trailing country code (only when it equals to country) and ##### if they exist

My code in Expression builder:

replace(ORIGINAL_DESCRIPTION_TEXT, COUNTRY_CODE, substring(ORIGINAL_DESCRIPTION_TEXT, 0, instr(ORIGINAL_DESCRIPTION_TEXT,'#')-1))

and this is not working as I expected

(Out put is : UNILEVER GROUP ##### UNILEVER GROUP )

The Expected output is UNILEVER GROUP.

Please help me to solve this problem.

Answer 1

I have reproduced the above and able to achieve your requirement using iif in dataflow expression like below.

iif(and(greater(instr(ORIGINAL_DESCRIPTION_TEXT,'#'),0),greater(instr(ORIGINAL_DESCRIPTION_TEXT, $country_code),0)), substring(ORIGINAL_DESCRIPTION_TEXT, 0, instr(ORIGINAL_DESCRIPTION_TEXT,'#')-1), ORIGINAL_DESCRIPTION_TEXT) 

Here country_code is a parameter and I have given the values as 'GBR'.

If the ORIGINAL_DESCRIPTION_TEXT column contains '#' and country_codey, then it stores the string from start to the index of '#'. If not, it stores the same column without any changes.

Result: