question

AbhishekVyas-4546 avatar image
0 Votes"
AbhishekVyas-4546 asked MartinJaffer-MSFT commented

Concatenate dynamic columns in ADF

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.

azure-data-factory
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

concat(toString(columns()))

0 Votes 0 ·

@AbhishekVyas-4546 if you found your own solutioin, could you please share it with the community?

0 Votes 0 ·
nasreen-akter avatar image
0 Votes"
nasreen-akter answered

Hi @AbhishekVyas-4546,

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')), '')), 
             ''
         )
     )
 )

77866-img23.jpg

Thanks! :)





img23.jpg (94.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AbhishekVyas-4546 avatar image
0 Votes"
AbhishekVyas-4546 answered AbhishekVyas-4546 commented

@nasreen-akter. The solution is not dynamic in nature. Column names are dynamic.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AbhishekVyas-4546, 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(). :)

0 Votes 0 ·

@AbhishekVyas-4546 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.


0 Votes 0 ·

@MartinJaffer-MSFT

Can you please post an example?

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

@AbhishekVyas-4546

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)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.