Concatenate dynamic columns in ADF

Question

Hi Team,

How do we create expression builder to concatenate the dynamic columns in dataflow?
Or if there is any other way to achieve the same?

Following are the use cases where source file may have couple of columns (for addresses) which are to be combined to store in one column (FullAddress) in the sink.

a. Address1, Address2, LineAddress3 -> FullAddress (derived column)
b. Address2, Address1, City -> FullAddress (derived column)
c. City, Address2 -> FullAddress (derived column)

Please note that the order and the number of columns may vary depending on user.

Answer 1

Hi @Abhishek Vyas ,

Hope you are looking for something like

iif(!isNull(toString(byName('Address1'))) && !isNull(toString(byName('Address2'))) && !isNull(toString(byName('LineAddress3'))),  
    concat(iifNull(toString(byName('Address1')), ''), iifNull(toString(byName('Address2')), ''), iifNull(toString(byName('LineAddress3')), '')),   
    iif(!isNull(toString(byName('Address2'))) && !isNull(toString(byName('Address1'))) && !isNull(toString(byName('City'))),  
        concat(iifNull(toString(byName('Address2')), ''), iifNull(toString(byName('Address1')), ''), iifNull(toString(byName('City')), '')),   
        iif(!isNull(toString(byName('City'))) && !isNull(toString(byName('Address2'))),   
            concat(iifNull(toString(byName('City')), ''), iifNull(toString(byName('Address2')), '')),   
            ''  
        )  
    )  
)  

Thanks! :)

Answer 2

@Nasreen Akter . The solution is not dynamic in nature. Column names are dynamic.

Answer 3

@Abhishek Vyas

If all the colums are strings, you can use

byNames($myColumnNames)  

Otherwise it gets more complicated.

reduce(array(byNames($columnames)),'',concat(#acc,toString(#item)),#result)

Answer 4

Hi,

I want to improvise the requirement, consider before i perform the column concatenation, i want to perform the trim on each column. Below solution will not be sufficient.

concat(toString(columns()))

Thanks,

Naveen