Is it possible to do row reference in ADF data mapping flow

Question

We received a dataset with data broken out in tranches by category, and would like to put the tranche breaker "Department" into its own column, so the output should be "department", "employee code", "salary" and remove the extra rows at the bottom. Is there anyway to achieve this?

Sample output

Answer 1

In order to create a new column called department and fill the data in the column, you can follow the below steps.

1. source transformation is taken for input data.

2. derive transformation is taken to add a new column called "Department". The expression for this column is given as iif(isNull(toInteger(EmployeeCode)),EmployeeCode,toString(null())). Another column named dummy is added and value is given as 1.

3. Surrogate Key transformation is added to generate a surrogate key for each row in the data flow.

4. window tansformation is taken to fill the missing values in the "Department" column using the last function.

5. filter transformation is taken to filter out the rows where the "Salary" column is null. and then select transformation is taken to select the "EmployeeCode", "Salary", and "Department" columns.

Reference: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-script#fill-down