Use the Transpose tool to pivot the orientation of the data table. It transforms the data so you may view Horizontal data fields on a vertical axis. The Transpose tool is the reverse application of the Cross Tab tool, which pivots the orientation of the data from vertical to horizontal.
This tool is useful for extracting non-conforming data. There is no limit to the number of records or fields that can be transposed.
Configure the Tool
- Key Columns: Select the columns (fields) to pivot the table around. This name remains on the Horizontal axis, with its value replicated vertically for each data field selected.
- Data Columns: Select all the columns to carry through the analysis.
- Use Select All to check all of the options, or Deselect All to clear all selections.
- Missing Columns: Select the action that you want to take if columns are missing:
- Error: Throw an error in the Results window and stop processing records.
- Warn: Throw a warning in the Results window, but continue processing records.
- Ignore: Ignore missing columns and continue processing records.
Key Columns Example
The Key Columns you select replicate vertically and create a row for each Data Column you select to transpose.
For example, you have a table containing 2 rows and 4 columns. When you transpose the table and select a Key Column without deselecting any Data Columns, you end up with 6 rows and 3 columns along with the selected Key Column, Name (represents the name of the remaining selected Data Columns), and the Value of the selected Data Columns.
If you deselect the Data Columns, the number of rows after the Transpose tool decreases.
The number of rows you end up with after transposing the table is equal to the number of initial rows multiplied by the number of Data Columns selected, minus any columns you selected as Key Columns.