Can we select rows with duplicate entries in Alter Row transformation in Azure Data Factory?

Question

There are two columns:

col_A	col_B
111	2.0
222	1.0
222	2.0
333	1.0

Using alter row transformation i would like to select rows that have repeated entries in col_A, in this example 222 and select the corresponding highest value in col_B i.e. 2.0

The output should look as follows:

col_A	col_B
111	2.0
222	2.0
333	1.0

Answer 1

You can use Aggregate transformation in Data flow.

This is my source data:

In the Aggregate transformation, give col_A for group by to select the distict rows and col_B for aggregate. Use the max(col_B) in this to get the max values.

Group By:

Aggregate:

Result:

Update about Extra Columns:

To Carry the Remaining columns after aggregation transformation, use Join transformation with Join type as inner join(Join of above with source) and give the above two columns as Keys.

After Join, you can remove the extra columns with select transformation.

Result with Extra rows: