Issue while adding expression in dataFlows in Azure Data Factory

Sampath S 81 Reputation points

I am new to DataFlows in ADF.

My input is a excel file having 20 columns.All the values are coming as integers.Some column have string values as xyz and pqr.I want to update all the string value(ie xyz and pqr) as Blank in my Sink Database.How can I do that in Dataflows.How can i write the expression to update the 2 strings to blank?

Sink :SQL server database

@Suba Balaji

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,087 questions
0 comments No comments
{count} votes

Accepted answer
  1. AnnuKumari-MSFT 32,001 Reputation points Microsoft Employee

    Hi @Sampath S ,

    Thankyou for the follow up query. I understand you want to convert all the strings throughout the dataset into NULL. Please let me know if that is not the case.

    You can make use of column pattern in derived column transformation .

    • In the column matching condition , provide type=='string'
    • In column name expression , provide $$
    • In value expression , provide toInteger($$)

    Kindly refer to the below gif for more details:

    Hope it helps. If the suggested response helped you, please click "Accept Answer" and kindly upvote the same.

    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee

    Hi @Sampath S ,

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

    If understand correctly, in some of your columns you have values intergers also few values as strings as xyz or pqr and you would like to take that values as nulls or blanks in to sink. Please correct me if I am wrong.

    You can consider using derived column transformation on top that column and use toInteger(). This function returns null when cast connect happen.

    Please check below gif for better idea.

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


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification