Concatenate dynamic columns in ADF

Question

Concatenate dynamic columns in ADF

Abhishek Vyas 101

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.

Kiran-MSFT 696 Reputation points Microsoft Employee Moderator

2021-03-15T20:00:30.727+00:00

concat(toString(columns()))
MartinJaffer-MSFT 26,161 Reputation points

2021-04-05T17:13:52.21+00:00

@Abhishek Vyas if you found your own solutioin, could you please share it with the community?

5 answers

Your answer

Kiran-MSFT 696 Reputation points Microsoft Employee Moderator

2021-03-15T20:00:30.727+00:00

concat(toString(columns()))
MartinJaffer-MSFT 26,161 Reputation points

2021-04-05T17:13:52.21+00:00

@Abhishek Vyas if you found your own solutioin, could you please share it with the community?

Answer 1

MartinJaffer-MSFT 26,161

@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 2

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 3

Abhishek Vyas 101

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

Nasreen Akter 10,891 Reputation points Volunteer Moderator

2021-03-15T16:26:54.9+00:00

@Abhishek Vyas , Not completely sure what you mean. But I think it's dynamic in a sense that the expression will not failed if the Columns in the Dataset are not present. ByName() function will only return value if you have the column, otherwise it will give you NULL. "columns are dynamic": in the same Dataset, if you want to pass different files with different schema with different column ordering, you have to use ByName()/byNames(). :)
MartinJaffer-MSFT 26,161 Reputation points

2021-03-15T18:17:36.603+00:00

@Abhishek Vyas do you mean the Column names change?

If you have an array of column names, then it is possible to use the map and reduce functions to transform and concatenate them.
Abhishek Vyas 101 Reputation points

2021-03-17T17:11:29.167+00:00

@MartinJaffer-MSFT

Can you please post an example?

Answer 4

Anonymous

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

Answer 5

Mohammad Soori 0

I used this command as a Derived Column, and it works fine for me:

concatWS('~',toString(columns()))

'~' is a delimiter.

Share via

Concatenate dynamic columns in ADF

5 answers

Your answer