Welcome to Microsoft Q&A platform and thanks for posting your query here.
As I understand your question, you want to transform your data from csv to nested json format using mapping dataflow in ADF pipeline.
To achieve the same, you need to follow the below steps:
- Add a pivot transformation after source , and group by using 'Id' column . Use 'Type' Column for pivot key . Use
max(value)
for Pivoted column expression
- In the data preview tab of pivot transformation, hit on
Map drifted option
It will redefine the schema:
- Use derived column transformation to create a new column called 'typedata' and create subcolumns within typedata as type1 and type2
- Use select transformation to deselect type1 and type2 columns :
- Use sink transformation with json dataset to load the data into json file and call the dataflow in a new ADF pipeline and execute it. Here is how the output would look like:
Hope it helps. Kindly accept the answer and take the survey if the answer was helpful. Thankyou