 Hello, welcome to SSNRitape, so say this side and today we are going to see about the derived column transformation. So what is derived column transformation? So derived column transformation to use the derived column transformation to generate new columns in your data flow or the modify the existing fields. Second is the creating or updating the columns when creating a derived column you can either generate a new column or update the existing column. So this is basically we can say that we are having a input and on that input we are having certain columns. So if we don't want to load that data directly into the destination we just want to do certain modification on the existing columns or we want to create a new column and that column will have some data additionally. So we want to load all those. So for that purpose we can use the derived column transformation. So go to on the browser and we will see the practical. So here first of all you can see under this SSU testing blob store is under the input folder. So in this container we can see this employee India file. So this employee India file is having four columns with the employee ID name address and department. So under the department we could see IT HR departments. So instead of IT or HR we want to generate a new column with the department full name and full name will be having like information tech and for the HR that will be human resource. So the full name we want to load and here we can see if somewhere in the employee address value is null then instead of null we want to keep this as unknown. So in our output that will be having five columns employee ID employee name employee address department name and department full name. So for this after making the changes so the destination file we want to copy from this input to the output of this folder. So as of now in the output we don't have any file. So we want to copy from there to here but we want to do some additional things that we have discussed. Now go to on the Azure data factory. Let me try to add a new data flow here and let me call this data flow as derived column transformation now here we need to add the source. So as I have already created the source for the employee file so I am going to use the same. So here we can select from this data set so employee source input. So we have created this data set earlier in this video series. So we can click on open and quickly solve that. So it is referring the input folder and this is the file that we have saved. Now we can go here so everything is okay. Here we can see the plus symbol so we can click on this plus symbol and here we can see option for the derived column. So we can click on that derived column. Now under this derived column here we can see that the output stream name so we can call the output stream name whatever you want and whatever the incoming stream so our source is the source one so this is selected here. Now here we can see the columns so we can modify the existing column values or we can add a new column value so that option is here. So here we can see add or select a column if we are going to select the existing column so it will be modifying in the existing column if we are going to write some name here for example I am going to add the department full name here so we can write like this. After that we can go under the expression and we need to open the expression builder. So in the department name we are having the values like it and hr so we want to replace the it and hr values with the human resource and information technology. So for that we can use the case statement or we can use the if condition so I am going to use the if condition here we will use the case statement so I am going to use the case statement here we will use the if condition later so we can call the case like this so here we have to specify the condition so what will be the condition so our condition is your department name value that should be equals to it and if this is true then we want to have the value like information technology like this now if this is not it so on that case it is hr so we can call this as we can call this as human resource like this so this is all about the case statement that we have so if we are having more than two conditions so on that scenario we have to use the like we need to copy this here and after that we can paste it here and put comma so this time this is for the hr now we can save and finish it so it will be adding one more column with the department full name and that will have information technology or the human resource now go to the data preview and try to refresh it so this should have five columns now four columns from the source and one column it is nearly we have added in this derived column transformation so that you can see like the department full name so now here we can see that like the department full name is having information human resource whenever the department name is it or hr here we can see the employee ID 5 which is the smith the employee address which is null so on that case we don't want to display this as null we want to have the unknown so how we can do that so in this case we need to update in the existing column so go to the settings again and here we can see this plus symbol so we can click on that so it is saying add a column so we can click on the add column here we need to select the employee address then we need to go in the expression and under this expression so here I am going to use the if condition so if the first expression the condition we need to set so here we need to check is null so under the is null what we need to check we need to check for the employee address if this employee address is null so it is saying some error here let me try to remove this and try to get the is null value now so this is like that now here we need to specify the expression for the employee address if this value is null on that case we want to show this as unknown otherwise we want to display the employee address we can close the bracket then we can save and finish it go to the data preview and try to refresh it again so this time it will be having the unknown instead of null so we can verify that so here we can see the unknown for the Smith so this is we can update or we can also update the employee name in the small letter or the capital letter so that we can also do so we can modify whatever is our requirement now let me put this in the sync so let me add sync here so under the sync let me go and try to get by using the inline query so here let me search for the delimited text after that we can select the links service go to the settings we can browse then we can go in the output folder and after that we can click on ok now here we can see the option to file name so let me add output to single file and the file name is here so what will be the file name so the file name that should be employee data now we can publish it so it is saying output to sync okay so this is the error we have already seen in the earlier videos we need to go in the sync side and we need to set the partition to the single one now we can do the publish now we can publish it so in between let me try to add a pipeline and this pipeline will be calling this data flow to execute so let me search for the data flow activity here under this data flow let me go to the settings and try to add the derived column transformation now we can try to debug it so once this debug will be completed the files should be available into the output folder with the name of employee data so as of now here we don't have any file so your pipeline is executing so that we can see that is in progress so it is completed successfully now we can go in the output folder and try to refresh it so it should have the data with the employee that we can see we can open this file and we'll try to verify the data go to the edit and here we can see the data we cannot see the header here because in the data flow we did not specify the first row as header in the settings so here we can see that option if you are going to select this we will see the header as well so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos see you in the next video