Assign results to a variable in derived columns

Question

In Azure Data Factory, I have two CSV files that I am pulling into to transform and output to JSON. I would like to use results from one column in another. For example

I am using derived columns If I have a column Id that pulls information from box44. I want to use avoid using the case statement in every column that requires these values. What would be the best method to pass the results of the case statement to other columns?

Id=case(
box44 =='Test1' , '124',
box44 == 'Test2' , '456',
box44 == 'Test3' , '789',
box44 == 'Test4' , '1011',
box44 == 'Test5' , '1238'
)

Answer 1

Hello @Vertta Moody ,

Thanks for the question and using MS Q&A platform.

From your question, you have a derived column in your Dataflow that has below transformation :

Id=case(  
box44 =='Test1' , '124',  
box44 == 'Test2' , '456',  
box44 == 'Test3' , '789',  
box44 == 'Test4' , '1011',  
box44 == 'Test5' , '1238'  
)  

You'd like to re-use this output in other transformation in your dataflow. If this is not the case, can you please help me with additional context ?

I made use of the Parameter feature in the Data Flow.

I had a derived column similar in the question.

Now at the dataflow level :

I created a new parameter

In a Derive Column Transformation. I made the below mapping

Output :

Hope this will helps. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote 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
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators