Unpivot transformation in mapping data flow

APPLIES TO: Azure Data Factory Azure Synapse Analytics


Try out Data Factory in Microsoft Fabric, an all-in-one analytics solution for enterprises. Microsoft Fabric covers everything from data movement to data science, real-time analytics, business intelligence, and reporting. Learn how to start a new trial for free!

Data flows are available both in Azure Data Factory and Azure Synapse Pipelines. This article applies to mapping data flows. If you are new to transformations, please refer to the introductory article Transform data using a mapping data flow.

Use Unpivot in a mapping data flow as a way to turn an unnormalized dataset into a more normalized version by expanding values from multiple columns in a single record into multiple records with the same values in a single column.

Screenshot shows Unpivot selected from the menu.

Ungroup By

Screenshot shows the Unpivot Settings with the Ungroup by tab selected.

First, set the columns that you wish to ungroup by for your unpivot aggregation. Set one or more columns for ungrouping with the + sign next to the column list.

Unpivot Key

Screenshot shows the Unpivot Settings with the Unpivot key tab selected.

The Unpivot Key is the column that the service will pivot from column to row. By default, each unique value in the dataset for this field will pivot to a row. However, you can optionally enter the values from the dataset that you wish to pivot to row values.

Unpivoted Columns

Screenshot shows the Unpivot Settings with the Data Preview tab selected.

Lastly, choose the column name for storing the values for unpivoted columns that are transformed into rows.

(Optional) You can drop rows with Null values.

For instance, SumCost is the column name that is chosen in the example shared above.

Image showing the PO, Vendor, and Fruit columns before and after a unipivot transformation using the Fruit column as the unipivot key.

Setting the Column Arrangement to "Normal" will group together all of the new unpivoted columns from a single value. Setting the columns arrangement to "Lateral" will group together new unpivoted columns generated from an existing column.

Screenshot shows the result of the transformation.

The final unpivoted data result set shows the column totals now unpivoted into separate row values.

Next steps

Use the Pivot transformation to pivot rows to columns.