Identify record failing in a Select Action (Error due to conversion from number to string)

[Sam4]

Hi, I have a flow that extract the records from a table in Excel and then, using a Select, applies some formulas to format the data. However, when there is some bad data, the Select fails. I would like to get which record (row or column has been impacted by this)

For example, I have the following flow. The cells in yell

Select Conversion has these formulas:

{
  "ID_New": @{item()?['ID']},
  "DateIn_New": @{int(item()?['DateIn'])},
  "MoneyIn_New": @{float(item()?['MoneyIn'])}
}

If the data from the table is correct, this flow works fine. However, the issue is when the user insert some bad data. For example, the records in yellow as below

  In this case, [Select Conversion] Action will fail because it is trying to convert some data as int("TBC") and, as expected, failing.

For those cases I'm configuring a Run After and sending an email depending if [Select Conversion] was a success or a failure

The issue is I want to include in the Error email more details about the error. Ideally which cells failed (C4, E4, D6, F6, F9) and if not possible at least the Record or the Column. Something like:

- There was an error in Record 4, 6, 9

- There was an error in Column DateIn, DateOut, MoneyIn and MoneyOut

Worst case scenario I could manipulate the error message from the Select and include it in the email, however, this doesn't seems to be possible.

InvalidTemplate. The execution of template action 'Select_Conversion' failed: The evaluation of 'query' action 'where' expression '{ "ID_New": "@item()?['ID']", "DateIn_New": "@int(item()?['DateIn'])", "MoneyIn_New": "@float(item()?['MoneyIn'])" }' failed: 'The template language function 'float' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

Also, and to simplify things, I don't want to use "Apply to Each" as final solution will have more columns and rows and the performance will be impacted.

Does anyone know how to put more details on the record that failed or how to extract the error message from the Select?

Any help is more than welcome

Kind Regards

S

[ManishJain]

HI @Sam4 ,

While you are doing select to get the data from excel and process it , before you do this action please use filter action to filter out bad data i.e. Date IN is not empty , Date OUT is TBC . This filter action can be used to filter both ways i.e. bad data and good data and store it in array for further processing to get rows with bad data.

Thanks