Grouping and eliminating excel columns

[Meyammai]

Good day,

It would be helpful if someone could share their 2 cents on how to handle this scenario.
Below is a sample of data that I have to process:

I have to eliminate the duplicates and group the record based on the Node and Description with its highest priority.
I would need the below result:

Are there functions/activities that I can use for this scenario?

Thank you,
Meya

[prasath17 (Guru)]

@Meyammai - Please check this…

 (From d In Dtinput.AsEnumerable
 Group d By k=d(0).toString.Trim, k2=d(1).toString.trim,k3=d(2).toString.trim Into grp=Group
 Let ra = New Object(){k,k2,k3}
 Select dtGrouped.Rows.Add(ra)).CopyToDataTable

[vinithareddyk123 (Vinitha)]

Hi @Meyammai
You can also try this:
convert your excel as Datatable using Read range activities,
Later you can use Remove Duplicates row activity and Sort Datatable Activity.

[Manish540 (Manish Shettigar)]

@Meyammai Check this below link,

How to group by multiple columns from a datatable

@491114473 Have a Look Here for an example with 1 Key In your Case a linq could Look Like following (From d in dt.asenumerable Group d by k1=d(0). toString.trim.substring(0,10), k2=d(1).toString.trim Into grp=group Let s= grp.sum(function (x) CDbl(x(2).toString) Let ra = New Object(){k1,k2,s} Select dtResult.rows.add(ra)).copytodatatable dtResult is Just an empty datatable with the needed Output structure. Often IT can be Cloned from Origin datatable with dt.clone Statement. Expect som…

Hope this may help you

[ppr (Peter)]

@Meyammai
your case is about grouping and custom sort

give a try on following:
create a dictionary string, int3 and store the Prio Values along with its sort rank
dictCustomOrder =
new dictionary(Of String, Int32) From {
{“High”, 1},
{“Medium”, 2},
{“Low”,3}
}

Use an assign activity
left side: dtResult | Datatype: DataTable
right side:
(From d In dtData.AsEnumerable
Group d By k1=d(“Node”).toString.Trim, k2=d(“Description”).toString.Trim Into grp=Group
Let go = grp.OrderBy(Function (x) dictCustomOrder(x(“Priority”).toString.Trim))
Select go.First()).CopyToDataTable

[Meyammai]

Hey,

Is it possible to show a screenshot of what you are describing?
I have not used dictionary activities before and find it a bit hard to follow.

Thank you in advance!

[ppr (Peter)]

Variables:

Flow:

Statements taken from above

Find starter help here:
GroupBy_2Col_GRPCustomSort_TakeFirst.xaml (8.5 KB)

[Meyammai]

Thank you so much @ppr !
It works just as I was expecting.

Thank you!!