I have a dataset and i wanted to delete the row or remove the row once the status is “completed”. Do you know how I can do that? I don’t want to read range and filter the table since it will mess up the format of the other cells.
Input
Output
Hi @ndtran85 ,
Sure try the following steps,
Step 1 : Read the data and store it in a dataTable variable.
Step 2 : Use a For each row in data table activity to iterate through the table.
Step 3 : Inside the for loop by default you will be using CurrentRow as row reference, place a if condition CurrentRow(“Status”).Tostring.Equals(“Completed”), in Then part of of condition add Remove data row/column activity, and set the data row property as currentRow and pass the name of data table in which the table data is stored.
Step 4 : Post deletion of row containing completed status, you can print it in an excel using write range activity or manipulate the data further as per your need.
Let me know if you have any difficulties in any of the steps above.
Hope this helps you out.
Thanks,
Gautham.
Hi @ndtran85
=> Read Range Workbook with enabling preserve Format in properties.
Output-> inputDt
=> Use below syntax in Assign:
inputDt = inputDt.AsEnumerable().Where(Function(row) row.Field(Of String)("Status") <> "Completed").CopyToDataTable()
=> Write Range Workbook inputDt
Sequence.xaml (7.4 KB)
Let you know if you have any queries.
Regards
Hi @Parvathy
Thank you so much for the solution. It works. However, even if I enabling the “preserve format” the formula (countif function) in column “% completion” is not there anymore. Also, there are other conditional formatting rule is gone when we write range to another worksheet. Is there anyway we can just delete the row with status “completed” without write range to another worksheet?
Are you working with Excel activities @ndtran85. Please specify, according to that, I will give you the code.
Regards
Hi @Parvathy,
Yes, i’m working with excel. Here is the screenshot of all the columns. I set the conditional formatting to color any with “Yes” with green color in column K to M. I already have the function countif in column P to calculate the percentage of completion. That’s why if I write range the workbook after remove the row with status “completed”, all other formatting in the excel was erase. Thus, if we can just remove the row certain status to their appropriate status, it would be great.
Thank you so much @Parvathy! You are amazing!
Use the Filter Data Table activity.