Using Azure Data factory, how to merge multiple rows into one row?

Question

I have data coming up from the source in this way :

Key | Value
10 | 101
10 | 102
10 | 103
10 | 104
20 | 201
30 | 105
30 |106
30 | 107
40 | 432
40 | 444
50 | 120

My output should look as follow:

Key | value
10 | 101,102,103,104
20 | 201
30 | 105,106,107
40 | 432,444
50 | 120

This in SQL Server could by acheived by FOR XML PATH or String_Agg(), but how can we do the same using Azure Data factory transformations? Unable to find String_Agg() in Azure Data factory. Is there any other method for this? Please help asap.

Answer 1

Use an Aggregate transformation in a data flow with grouping and the collect() expression function.

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-script#string_agg-equivalent

Answer 2

Thanks a lot Mark, it was helpful. However, how can I get distinct values from the groups? collect() is fetching all the values (including duplicates)

Answer 3

I'm not using ADF, just KQL. Have achieved output shown in screenshot, but want to see one row instead of two rows (maybe CSV) for same VM (it does gives distinct value) but need as one row showing respective status of all those corresponding services for the VM. Can anyone help with this req. in KQL please !