How to filter the numeric and alphanumeric values of the column using copy activity in Azure data factory

Question

I have 16k rows from the SharePoint
one of the rows has string, float and integer values
I want to convert if the row value is integer or float then leave else change into 0 using azure copy activity

Answer 1

Hi @Prabhakar Karunakaran - Consultants ,

Thank you for posting query in Microsoft Q&A Platform.

Please Note, Copy activity in ADF, focuses only on Data movement that means copy data from source to sink storages. To perform any kind of transformations we should consider using dataflows only.

Dataflows do not support SharePoint as source at this moment, Hence I would suggest you copy data as is to ADLS gen2 and then use dataflows to take data from ADLS gen2. Inside dataflow use derived column transformation to write condition as per your requirement.

Consider writing expression as below. Here I am considering id as column name on which we are applying rules.
iif(!isNull(toInteger(id)) || !isNull(toFloat(id)),id, 0)

Please check below video to understand about derived column transformation.
Derived Column Transformation in Mapping Data Flow in Azure Data Factory

Hope this helps. Please let me know if any further queries.

-----------

Please consider hitting Accept Answer button. Accepted answers help community as well.

Answer 2

5e-05

5e-05

1.5045

above data is coming in source i need only numeric value i.e 1.5045

above code : iif(!isNull(toInteger(id)) || !isNull(toFloat(id)),id, 0)

dint worked for me ... could anybody help me