Academy
Transformation

Pivot Columns Step

The Pivot Columns step reorganizes table data, transforming information from rows into new columns. Use it when you need to change the data orientation to facilitate analysis or presentation.

How to configure

Fill in the fields below to define the pivot:

  • New Column From: select the existing column whose values will create the new columns.

  • New Column Names: shows the names of the columns to be created and allows you to exclude those you do not want to keep.

  • Fill with Values From: indicates the column whose values will populate the new columns.

  • Output Aggregator Method: defines how data is grouped after selecting the values column. Options vary depending on the data type.

  • Column Selection: allows you to choose one or more columns to be displayed in the output port.

The available aggregator method depends on the values column's data type:

  • Alphanumeric: First and Last.

  • Numeric: Sum, First, Last, Minimum, and Maximum.

  • Date: First, Last, Minimum, and Maximum.

Record limit: the first 500 records are loaded, and it is possible to select up to 100 at a time. To process the remaining records, create a new Pivot Columns step and load what was missing.

Usage example

Consider a table with columns Date, Company, and Revenue, where each company appears on more than one date: Legal Company with 10,000 and 12,000, and Super Ltda. with 30,000 and 40,000.

The goal is to transform the companies into columns and sum the revenue for each. To do this, configure:

  • New Column From: Company.

  • New Column Names: keep the defined options.

  • Fill with Values From: Revenue.

  • Output Aggregator Method: Sum.

  • Column Selection: uncheck the Date column.

The result is a table with two columns, Legal Company (22,000) and Super Ltda. (70,000), each with the total summed revenue.

  • Unpivot Columns Step

Was this helpful?
Was this helpful?