I want a method to achieve this

Solution of Mr.YoiChi san is too short
you can try it,
my way is easier to understand but it is logger.

Well, understanding your way is shorter for me in the long run.

What if there are more columns like vehicle_name , how will the query change

HI,

If Id column always exists at the first column, the following expression will work even if any number of columns exists.

dt.AsEnumerable.GroupBy(Function(r) r(" id").ToString).OrderBy(Function(g) Cint(g.Key)).SelectMany(Function(g) g.Select(Function(r,i) dt.Clone.LoadDataRow({if(i=0,r(0),"")}.Concat(r.ItemArray.Skip(1)).ToArray,False))).CopyToDataTable

Sample20230816-3L (3).zip (14.3 KB)

Regards,

for me , after the vehicle_name column , other columns are showing their values one rows below the previous column.

Can you share specific sample as file? It’s no problem if dummy data.

Result1.xlsx (8.8 KB)

Is this input file? Can you also share expected output?

vehicle_name column is fine , other columns have their values shifted down by one row, i just want values for other columns adjacent to their ID’s

Sorry i am making it confusing , this is my data , just convert it such that each id has values of other columns adjacent to it.
Data.xlsx (8.9 KB)

Hi,

Is the following what you expect?

image

If so, the following will work.

dt.AsEnumerable.GroupBy(Function(r) r("ID").ToString).OrderBy(Function(g) Cint(g.Key)).Select(Function(g) dt.Clone.LoadDataRow(Enumerable.Range(0,dt.Columns.Count).Select(Function(i) String.Concat(g.Select(Function(r) r(i).ToString))).ToArray,False)).CopyToDataTable

Sample20230816-3L (4).zip (13.9 KB)

Regards,

Actually the output is slightly different , can you try it for this file
Data.xlsx (8.9 KB)

just convert it such that each id has values of other columns adjacent to it.

Can you also share expected output file?

Yeah output should be like this
Result.xlsx (8.7 KB)

I understand your points.
How about the following?

dt.AsEnumerable.GroupBy(Function(r) r("ID").ToString).OrderBy(Function(g) Cint(g.Key)).Select(Function(g) dt.Clone.LoadDataRow(new object(){g.Key}.Concat(Enumerable.Range(1,dt.Columns.Count-1).Select(Function(i) String.Concat(g.Select(Function(r) r(i).ToString)))).ToArray,False)).CopyToDataTable

Sample20230816-3L (5).zip (15.3 KB)

Regards,

1 Like

I am getting the output as
Result.xlsx (9.0 KB)

Hi,

Did you run the above sample as it is? In my environment output is as the following.

image

Regards,

The answer @Nguyen_Van_Luong1 will work.
Just need to sort the datatable by ID by using ‘Sort Data Table’ Activity or by using linq: datatable.Select().OrderBy(function(row) row(“id”)) and then iterate through each row using for each data row activity and assigning current id to a variable on each loop which is used compare with the next id. If the id is repetitive, just assign string.empty or nothing to id column while adding the data row to a data table you build using build data table activity.

You can try and let me know👍
Regards

Also add the copytodatatable at the end to convert it to data table:
datatable.Select().OrderBy(function(row) row(“id”)).CopyToDataTable

Regards

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.