Making rows of a column from a dataset to column names of different datset

Question

I have column names in a different dataset and actual data in different dataset, both the datasets can be connected by field ID. How can I make the rows of another dataset as column names in actual dataset?

I've attached sample data of two datasets for better understanding

dataset 1(column names):

dataset 2:

Desired output:

Thank you

Answer 1

Hello @Pujitha Chennupati ,

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

In order to make the rows of column from dataset1 to column names of dataset2, you will have to follow the below approach.

First you will have to flatten the array document from your source1 (dataset1), and then from your source2 (dataset2) you will have do a transpose, nothing but convert first column values as column names and second column values as values for the newly formed columns from column1 values. In order to achieve this, you will need to create a surrogate transformation
which will be used in the subsequent Unpivot transformation that will perform the transpose of your source2 data. The surrogate transformation column is used as ungroup by column in Unpivot transformation.

Setting up sources:

Transforming source2 data so that you can join the two sources to form the desired output. (Unpivot column Names to Rows/Column Values)

**Pivot column values/Rows to Column Names to transform the data as desired output. **

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][30]