Create a new column in Excel displaying TRUE or FALSE based on if it is numeric the value from another column

[derekma19]

Hi, 

I have some experience with Power Apps and Power Automate Cloud but I've just started my journey in PAD so apologies in advance if I am asking something obvious.

In my scenario I have an excel file with several columns. One of the columns have, or it is expected to have, numbers. However there ca be cases where there is bad data and instead of number we can find text strings or special characters. The expected outcome for this exercise is to separate the numbers from the bad data. That is, to pass from this:

MyValues

1

345

NA

23&2

245544

to this: 

MyValues	MyErrors
1	NA
345	NA
	Error
	Error
245544	NA

As I want this to be independent of human interaction I think the best approach is to use a unattended flow with PAD running in a VM / Hosted RPA bots. However not quite sure how it works so would like to know if someone can give me some hints on how to build this and if this is the best approach.

Can anyone help with that?

Ideally I would like to use  a SELECT in PA Cloud creating a column MyErrors based on the expression IF(ISNUMERIC([MyValues]),"NA","Error"). But as the function ISNUMERIC does not exist in PA I need to look for another option. The two options I have checked are:

1) Checking row by row if I can perform a math operation and configuring "Run After..." success and failed to write on that column. This solution is not valid as I can have +10000 of rows.

2) Passing the data to Power Apps where I can create a collection and a calculate column using ISNUMERIC (that it does exist in Power Apps) and then export the collection as CSV to SharePoint. This option works however I need manual intervention, among other things, to successful complete this case. 

Many thanks in advance

[ryule]

At a very high level, this is how I would accomplish it in PAD:

• Grab the column from Excel and put it in a list,
• loop through the list,
• determine if it's a number,
  • if it is, add it to a MyValues list and an NA to a MyErrors list
  • if it's not a number, add a blank to the MyValues list and an Error message to the MyErrors list

[Gidi]

Hi,

to do it with Power Automate you can have a look at https://powerusers.microsoft.com/t5/Building-Flows/How-to-know-if-an-variable-is-numeric/m-p/565652#...

In Power Automate Desktop the loop might take too long therefore I would interact with Excel via UI actions to create the needed data column. 

By the way: Unattended desktop flows needs special license with higher cost. 

[VJR · Accepted solution]

Hi @derekma19 

If anyways you need it Excel then you can do it there itself.

Find the formula that is working for you and add it in only the first row of the Excel column.

=ISNUMBER(A1)

Then use @Ankesh49's method of dragging the formula to the last available row of the sheet from this post here.

 Make sure to use the dollar sign on the formula. $A1