How do I do the Data Massaging as following in a data table?

Hi Team,

I am having a table as shown below:

Name Age Place Description Vendor
User 1 23 Pune This is UiPath
a description for
user one
User 2 24 Mumbai Demo of re UiPath
User 3 25 London UiPath is a autom UiPath
ation tool
User 4 26 Boston User four be UiPa
longs to the Boston th
in USA
User 5 27 Delhi This is the la Ui
st example to Pat
make this line comple h

And I wish to have a table as shown below:

Name Age Place Description Vendor
User 1 23 Pune This is a description for user one UiPath
User 2 24 Mumbai Demo of requirements UiPath
User 3 25 London UiPath is a automation tool UiPath
User 4 26 Boston User four belongs to the Boston in USA UiPath
User 5 27 Delhi This is the last example to make this line completed UiPath

I hope the requirement is clear, as I want to do the data massage such that data moving to multiple lines in the table is appended to previous lines.

Also There are empty lines in the raw data after each and every record, I wanted to remove that as well.

I know I can do it wit some messy unreliable complex string manipulations, but I want some clean and simple logic to satisfy these conditions.

@experts - @ppr @Krishna_547

Thanks and Regards,


  • fill up the empty Name values with the value from previous row
  • afterwards group by the data by Name and create the concated values from the group members

Can you please explain the activities and logic bit more in detail? Like for my case in particular…

User1, User 2, etc is just an example here. In real time, it can be "User 1, User 1, User 2, User 1, etc. "

Hi ,

I think the below steps should work :

Build DT with the required columns

  • For each row , if name column has some name then take tempVariable to append the contents of the next consecutive rows untill the name col has some value .

so , in the first iteration Name column has some name(User 1) so use variable to concatenate the contents of decription column and use add data row to add these text to the newly built DT.

Hope it is not that confusing :sweat_smile:

1 Like


would it look like this?

Name Age Place Description Vendor
User 1 23 Pune Line 1 User One UiPath
Line 2 User One
User 2 24 Mumbai Line 1 User Two UiPath
Line 2 User Two
User 1 23 Pune Line 3 User One UiPath
Line 4 User One

otherwise can you share this alternating User Block sample data with us? Thanks


What I meant there was that column one might have same Name multiple times.

So populating the empty cells in colum one willy nilly will confuse the logic since name data is not unique for every record.

Also, only thing i want was to merge those columns which are spreading their data into multiple lines keeping in mind that column one data can be used as a pointer to define a new record.

Hope this is clear now.


Vars & Preperation


Filling Up User Info:

Doing GroupBy and Concatenations with LINQ

(From d In dtData.AsEnumerable
Group d By k=d("Name").toString.Trim Into grp=Group
Let dc = String.Join("",grp.Select(Function (x) x("Description")))
Let vc = String.Join("",grp.Select(Function (x) x("Vendor")))
Let ra = New Object(){k, grp.First()("Age"),grp.First()("Place"),dc,vc}
Select dtResult.Rows.Add(ra)).CopyToDataTable

Worked with a reduced sample set:
After filling up:
After Grouping and concatenations:

Find starter help here:
GroupBy_1Col_FixGroup_ConcatCols.xaml (10.8 KB)

As you can see the defect on isa vs is a, but needed concatenation without seperator for another vs. an other we do have some cases for doing a fine tuning