The Transpose tool allows you to pivot the orientation of the data table. It transforms the data so you may view horizontal data columns on a vertical axis.
Similarly, you can use the Cross Tab Tool to pivot the orientation of the data from vertical to horizontal.
This tool is useful for extracting non-conforming data. There is no limit to the amount of rows or columns that can be transposed.
Configure the tool
Use Key columns to tell the Transpose tool which columns you want to see split out into rows, then use Data columns to configure which rows to split them into.
- Key columns: Select the column or columns to pivot the table around. The name of the column remains on the horizontal axis, with value replicated vertically for each data column selected.
- Data columns: Select columns to carry through the analysis.
- Missing columns: Select the desired behavior for missing columns. Error - the tool stops processing and displays an error message. Warn - processing continues and the tool displays a warning message. Ignore - missing columns are ignored.
Key column example
The Key column you select replicates vertically, creating 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 will 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 will end up with after transposing the table is equal to the Number of initial rows X the Number of Data columns selected, minus any columns you selected as Key columns.