You can do this easily if you get the below component installed.
- Read range to read the excel to a datatable
- The above mentioned component includes an activity named Data Table Consolidate. Using this activity, you can group the data table from the four key column combinations to get the maximum of the HDR_Seq column into another data table. So the aggregated data table will have the four key columns and the max of the Hdr_Seq column.
- Next we can use the two data tables we have and do a Inner Join using Join Data Table activity to filter the full dataset to get the required output
I have done it here for you as an example on how to use the component and to demonstrate how to get it done easily… You may tweak it as you need. It works perfectly to get your required output.
Make sure to install the above mentioned component before opening the file. You can install it through the Package Manager of the Studio
RemoveDuplicatesByGettingMaxValue.xaml (9.6 KB)
Let me know whether it helps…