question

BakhshpourDaniel-0131 avatar image
0 Votes"
BakhshpourDaniel-0131 asked PRADEEPCHEEKATLA-MSFT commented

Azure Data Factory Data Flow concatenating groups of 3 rows into a single row

Hello, I am new to azure data factory and am having trouble combining multiple groups of rows into one row using a dataflow.

I have a test file that looks something like this

HEADER
ACT1 data1
ACT2 data2
ACT3 data3
ACT1 data4
ACT2 data5
ACT3 data6
FOOTER

Every three rows of data within this file refer to one row within a database. What I am trying to do is concatenate every group of three rows together so I can load it into a database. I would want it to look like:

HEADER
ACT1 data1ACT2 data2ACT3 data3
ACT1 data4ACT2 data5ACT3 data6
FOOTER

What would be the best way to do this?

Thanks

azure-data-factory
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.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
1 Vote"
PRADEEPCHEEKATLA-MSFT answered PRADEEPCHEEKATLA-MSFT commented

Hello @BakhshpourDaniel-0131,

Welcome to the Microsoft Q&A platform.

We can achieve this using Mapping data flow. You may checkout the below steps:

Step 1: Add your data as source in Mapping data flow.

100517-image.png

Step 2: Add surrogate Key Transformation to get Incremental key column.

100530-image.png

Step 3: Add derived column transformation to generate new column with unique id for every 3 rows. So that we can utilize this column to group every three rows.

100582-image.png

Step 4: Add Aggregate Transformation and group By newKey Column as below and under aggregates a column with collect function as below.

100489-image.png

Step 5: Add derived column transformation to convert newHeader Column data to String.

100559-image.png

Step 6: take only NewHeader Column using Select Transformation with alias name as "Header" and finally load that data to Sink.

100539-image.png

Hope this helps. Do let us know if you any further queries.


Please "Accept the answer" if the information helped you. This will help us and others in the community as well.


image.png (144.3 KiB)
image.png (339.5 KiB)
image.png (395.2 KiB)
image.png (417.9 KiB)
image.png (409.4 KiB)
image.png (414.4 KiB)
· 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.

Hello @BakhshpourDaniel-0131,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

1 Vote 1 ·

Thank you for the help!

0 Votes 0 ·

Hello @BakhshpourDaniel-0131,

Glad to know it helped.

0 Votes 0 ·