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

Question

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

Answer 1

Hello @Bakhshpour, Daniel ,

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.

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

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.

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

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

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

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.