Flattening array column and creating Index column for array elements at the same time -- Azure Data Factory

Question

I have a dataset that in simple representation looks like:

Col1	Col2
1	[A,B]
2	[C]

I want to denormalize the data and create another column while flattening, which would be the index of the elements in the array. The desired result set would look like:

Col1	Col2	Col3
1	A	1
1	B	2
2	C	1

I was able to achieve the requirement using mapindex, keyvalues and mapassociation expression functions. Somehow I feel like this is not the right way to do it and there must be a better and easier way to do it. I read the microsoft documentation and couldnt find it.

Can someone help/guide me to a better solution?

Edit 1: Source is Azure Blob Storage. I have access to only ADF. Data is a complex XML document. All transformations are to be performed only with ADF.

Edit 2: Target is SAP BW . But I don't have control on it. I can only write to it.

Answer 1

You can use flatten transformation to flatten the array values and Window transformation to get the RowNumber, partition by Col1.

Flatten transformation: Unroll by array column (Col2).

Window transformation: Connect the output of flatten to Windows transformation.

• Set a partition column in the Over clause.
• Set a sort column to sort the data ordering.
• In window columns setting, you can define the aggregation rowNumber() to get the index value based on col1.

Output of Window transformation: