Loop each row, if the item is not empty get the header name

Help Activities
, ,
1

I’m a beginner in UiPath but this got me confusing.

This is what I’m trying to do. This is my sample data source in excel.

image

So I want to get the percentage of the “Amount” based on the header.

I don’t know if this is a good approach to my problem. Feel free to suggest any better solution.

I will be looping in each row, if the amount in the columns “Amount @ 10%”, “Amount @ 20%”, and “Amount @ 30%” is not empty I will trim away the “Amount @” and get the percent value. After that I will add the “percent” into another excel file.

Final output should look like this.

image

2

Hi @Shoji

Try this

image
image828×672 38.2 KB

Sequence.xaml (10.6 KB)

Output:

image

Regards,

3

Hello
Inside of the for each use this expretion (From col In dt.Columns.Cast(Of DataColumn)() _ Skip 2 _ Where Not IsDBNull( CurrentRow(col)) AndAlso Not String.IsNullOrEmpty(CurrentRow(col).ToString()) _ Select col.ColumnName).FirstOrDefault()

image

and it will give you the column name which find the first one that is not null

4

Build DataTable and configure result Table:
Columns: Name, Amount, Percentage
out: dtResult

Read range - ExcelFile: dtData

Assign Activity:
dtResult =

(From d in dtData.AsEnumerable
Let tl = d.ItemArray.Skip(2).Select(Function (x,i) Tuple.Create(x.toString,i)).ToList
Let tp = tl.First(Function (t) Not String.IsNullOrEmpty(t.Item1))
Let cn = d.Table.Coumns(tp.Item2+2).ColumnName
Let ps = System.Text.RegularExpressions.Regex.Match(cn, "\d+%")
Let ra = new Object(){d(0),d(1),ps}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

We used LINQ:

Also we can help you on decompose the approach and break it down to essential Activities, if you prefer