Columns to Rows

What does it do?

This module enables you to convert data in columns into data in rows.
The values from the transposed columns are merged into a single new column and the values for other columns are repeated for each row.

For example, suppose you have some data in this binary column format:

Binary Data Format

Rows to columns can help you transform your data into this format:

Binary Data Format

To convert columns to rows, you need to define sets of columns to group together and transpose.
The available settings and options for each group of columns are as follows:

Settings – Transpose Columns

Setting Description
Input Columns A set of selected columns to transpose into rows
Output Column Name The name of the output column that the selected input columns are transposed into
Include source column label If selected, adds an extra column to the output containing the source column name for the current item of data
Source Label Column Name If a source column is included (see above) this setting controls the column name given to that source column
Merge source label column If selected, allows you to specify a regular expression to be applied when populating the values in the source column.
For example, if your Input Columns are Q1_1, Q1_2 and Q1_3, applying a Merge Pattern of (?=_)\d will just extract the last digit from each.
So, the resulting Source Column Label values will be populated with 1, 2, 3 ass appropriate.

In addition, the following settings can be applied to the resulting dataset once the column groupings have been applied:


Setting Description
Collapse Duplicate Rows Automatically suppresses any duplicate rows, leaving only distinct rows
Remove Blank Rows Remove any rows where the data in all transposed column groups is blank. This can be useful for compressing your data if the input data is quite sparse (i.e. contains lots of blanks)


Download Columns to Rows Example

Tips & Tricks