How to dynamically use column NAMES (in value expression) from a parameter in ADF Dataflow Derived Column transformation?

Question

I'm working with Azure Data Factory Dataflow and trying to implement a solution where I need to dynamically reference column names in a Derived Column transformation.

Current Working Solution (Hard-coded)

Currently, I have this pattern that works successfully but requires hard-coding each column name:

iif(isNull($$), toLong(byName(replace('column1_cnt', '_cnt', '_cnt_avg'))), toLong($$))

This checks if the current column value is null. If it is, it gets the value from a column with a similar name but with '_cnt_avg' instead of '_cnt' suffix.

What I've Tried (Not Working)

I tried to make it dynamic like this, but it fails because '$$' represents the column's value, not its name:

iif(isNull($$), toLong(byName(replace('$$', '_cnt', '_cnt_avg'))), toLong($$))

Additional Information

I have a pipeline parameter $UpdateColumns containing a comma-separated list of column names (e.g., 'column1_cnt,column2_cnt,column3_cnt'). Ideally, I'd like to use this list to dynamically build my expressions.

Question

Is there a way to dynamically reference the current column's name (not just its value) within the Value expression of a Derived Column transformation? All I need is that I want to use column1_avg value if column1 value and assign the name as column1 is null but without hard coding.

Or is there an alternative approach to achieve dynamic column operations when dealing with multiple columns that follow the same pattern?

Answer 1

@Jaewon Song

To dynamically reference column names in a Derived Column transformation in Azure Data Factory (ADF) Dataflow, you can use a combination of parameters and expressions. Here's a step-by-step approach to achieve this:

1. Create a pipeline parameter that holds the list of column names. For example, UpdateColumns containing 'column1_cnt,column2_cnt,column3_cnt'.
2. Use the split function to convert the comma-separated string into an array of column names.
3. Use a ForEach activity to iterate over the array of column names.
4. Within the Derived Column transformation, use the byName function to dynamically reference column names.

Here's an example of how you can set this up:

Pipeline Parameter:

{
  "name": "UpdateColumns",
  "type": "String",
  "defaultValue": "column1_cnt,column2_cnt,column3_cnt"
}

Split the Parameter:

@split(pipeline().parameters.UpdateColumns, ',')

ForEach Activity:

• Add a ForEach activity to iterate over the array of column names.
• Inside the ForEach activity, add a Data Flow activity.

Derived Column Transformation:

• In the Data Flow, add a Derived Column transformation.
• Use the following expression to dynamically reference the column names:ple Expression

iif(isNull(byName($$)), toLong(byName(replace($$, '_cnt', '_cnt_avg'))), toLong(byName($$)))

Assuming you have a column name currentColumn in the ForEach activity, the expression in the Derived Column transformation would look like this:

iif(isNull(byName(currentColumn)), toLong(byName(replace(currentColumn, '_cnt', '_cnt_avg'))), toLong(byName(currentColumn)))

This approach allows you to dynamically reference and transform column names without hard-coding them, making your solution more flexible and maintainable

Hope this helps. Do let us know if you any further queries.