Transpose Data using ADF

Question

Hi,

It would be great if you could help me with the below scenario in ADF.

I have received the file in the following format and a new column will be added at the end for every week. Basically, this file will get dynamic column addition for the entire year.
For example: on 22/11/2021 new column will get added at the end as 14/11/2021 - 21/11/2021

Below is the sample source data.

We want to transpose the data in the following format to load into the target table(Azure sql).

Answer 1

Hello @Arul Karthic ,

Thanks for the question and using MS Q&A platform.

You could achieve your requirement using unpivot and pivot activity.

Source data :

Unpivot Action

You can configure the Unpivot Action as below :

Ungroup By : Type

This is the Type Column from the source. Note the upper case 'T 'in Type.

UnPivot Key : type

This is new column name - I saw in your example you had type in both scenario. So I continued to have the same. Alternatively, you could provide a diff name - like date etc. to avoid confusion

Unpivoted Columns: Value

Value is the name I have given at my end with String as the datatype.

You are converting the data in the format date:product:value

Output of the Unpivot Action

Pivot Action

Group By : Unpivot1@type

This is the Unpivot Key we used in the previous step (t - lower case)

Pivot Key : Unpivot1@type

Pivoted Columns : first(Value)

Output of Pivot :

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators