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:
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
