convert excel column headers as row headers in azure data factory

Question

need to convert below example

input from excel source -

id qn1 qn2 qn3 ................ max q50

1 qr1 qr2 qr3 ................max qr50

2 qr1 qr2 qr3 ................max qr50

desired output -

id qn qr

1 qn1 qr1

1 qn2 qr2

1 qn3 qr3

.

.

1 qn50 qr50

2 qn1 qr1

2 qn2 qr2

2 qn3 qr3

.

.

2 qn50 qr50

.

.

3

.

.

10

Answer 1

In your ADF pipeline, add a Data Flow activity.

Set up your source dataset pointing to your Excel file.

Then unpivot Columns:

• Use an Unpivot transformation to convert columns into rows. In the Unpivot transformation, you will select all the question columns (qn1, qn2, ..., qn50) to be unpivoted.
• Set the Key column to hold the column headers (qn1, qn2, ..., qn50) and the Value column to hold the responses (qr1, qr2, ..., qr50).

If you need to rename the unpivoted columns for clarity, you can add a Derived Column transformation to rename the Key and Value columns to qn and qr respectively.

Configure your sink to write the transformed data to your desired destination (for example another Excel file, a database...).