New to ADF and could use some help, I have a 2 column data set I would like to add an additional row to the data set. I have Columns : "CHANNEL" and "CHANNEL_ID" with values as following Source Data set and would like to add fields: '0' and 'blank' to those columns to produce the result belowDesired outcome. Is this kind of transformation possible within my dataflow?

I've tried to pivot the columns and add a derived column for the '0' field and then pivot those columns again, but I was not certain that what I did was right and I believe there has to be a simpler way than that.

CodePudding user response：

In order to add a new row channel_id=0 and channel='blank', I followed below approach.

• Two source transformations are taken with the same datasets as in below image.

• Then filter transformation is added to one of the source transformations and filter is given to select one of the rows from the dataset. filter condition:channel_id='52'

• derived column transformation is added and settings are give as,

Column: Expression
channel='Blank'
channel_id='0'

• Then Union transformation is added and derived column output and source1 output are given as input to the union transfromation.

Result of Union transformation:

Dataflow script

source(output(
channel as string,
channel_id as string
),
allowSchemaDrift: true,
validateSchema: false,
ignoreNoFilesFound: false) ~> source1
source(output(
channel as string,
channel_id as string
),
allowSchemaDrift: true,
validateSchema: false,
ignoreNoFilesFound: false) ~> source2
filter1 derive(channel =  'blank',
channel_id =  '0') ~> derivedColumn1
source1, derivedColumn1 union(byName: true)~> union1
source2 filter(channel_id=='52') ~> filter1
union1 sink(allowSchemaDrift: true,
validateSchema: false,
skipDuplicateMapInputs: true,
skipDuplicateMapOutputs: true) ~> sink1

By this way, you can add a row in dataflow.