Excel query- if value have more than 100 then pass or fail

Hello Friends,

I’ve provided a screenshot of two queries. 1. If the column c range contains more than 100% (highlighted in yellow), the result should be “Pass,” otherwise “Fail.”
2. If Column G has all hypens as seen in the screenshot, it should display “Fail,” else “Pass.”

Appreciate your help.

RPA Query 8-8-23.xlsx (9.0 KB)

Query - 1

(From row in dt.AsEnumerable.Skip(1)
Let result = If(Cdbl(row(2).ToString.Replace("%","")) > 100.00,"Pass","Fail")
Select result).ToArray

In Query - 1, we first go through each row except the 1st since it contains text like market price and so on. Then we remove the % at end and check if its greater 100%, if its then we set result as Pass else Fail. This will generate a set of values for each row as pass or fail that is converted to an array of string

Query - 2

if(dt.AsEnumerable.Skip(1).All(Function(row) row(6).ToString.Equals("-")),"Fail","Pass")

In Query - 2, we use an if condition to check whether all rows contain ‘-’ or not. The All function returns a boolean value that all rows in column G are hypen or not. If it results in True (i.e column G contains only hypens) then the output variable is set to False, else it is set to True. Output Variable is of type String

Note:- To execute this query, dt is the excel datatable used after read workbook

Bro, can you suggest me where I need to place this code to run?


As per the image above, the first query’s result will be stored in arr and second query’s result will be in result

It show this error.

I have used like this.

Can you show me your variables panel

I tried Arr datatype by changing string, int as well but have same error

Hey @Jeeru_venkat_Rao,

I have tried it in the below XAML: -
Main.xaml (9.8 KB)

Also since the column names in excel are same, I have disabled add headers and changed the code from Skip(1) to Skip(2)

Hi Bro,

Thanks for your help.
Still output is not correct. It shows Pass for all values.
I need to print Pass/Fail single time.
Below 100= Pass otherwise Fail


Second query is working perfect.

Also in any value “-” will can come. On that case it will not work. Can you script accordingly pls. See screenshot.

Hey could you update the query as row(2) instead of row(3) in the xaml, sorry for the confusion

(From row in dt.AsEnumerable.Skip(1)
Let result = If(Double.TryParse(row(2).ToString,0) > 1,"Pass","Fail")
Select result).ToArray

I already mentioned row(2) and also I tried with your latest code and it shows error.

I think it should be greater than 100 instead of greater 1

Hey @Jeeru_venkat_Rao, Could you try this

(From row in dt.AsEnumerable.Skip(2)
Let result = If(Double.TryParse(row(2).ToString,Cdbl(row(2).ToString,0) > 1,"Pass","Fail")
Select result).ToArray


(From row in dt.AsEnumerable.Skip(2)
Let value = If(Double.TryParse(row(2).ToString,Cdbl(row(2).ToString ,0), Cdbl(row(2),0)
Let result = If(value > 1,"Pass","Fail")
Select result).ToArray

When i tried to read the column in my system using UiPath, it gave me as 0.9 for 90% and 1.97 for 197%

I tried but this error occured. We are missing close bracket somewhere

It shows this error.

(From row in dt.AsEnumerable.Skip(2)
Let result = If(Double.TryParse(row(2).ToString,Cdbl(row(2).ToString),0) > 1,"Pass","Fail")
Select result).ToArray