Skip to main content

Transform a Column

The Transformer page provides several ways of rapidly transforming your data at the column level.

Tip

Except for finding and selecting columns, all of these actions add transformations to your recipe. After the step has been added, you can edit the recipe to adjust the step, including manually adding or removing columns.

Find Columns

For wide datasets, you can locate using either of the following methods.

Find by name

Steps:

  1. In the Transformer toolbar, click the Find column icon.

  2. In the Find column panel, begin typing the name of the column.

    CS-TransformColumns-FindColumnPanel.png

    Figure: Find column panel

  3. Select the column.

  4. The data grid is moved to display the selected column on your screen.

Find in Column Browser

You may find it easier to use Column Browser to find and select columns for transformation.

Steps:

  1. In the Transformer toolbar, click the Columns icon on the left side. Column Browser is displayed:

    CS-TransformColumns-ColumnView.png

    Figure: Column Browser

  2. You can scroll the columns to select one or more columns for transformation.

    Tip

    The Find column panel can also be used to locate columns in this list.

Select Columns

When you locate a column or columns of interest, you can select using one or more of the following methods:

  • Click an individual column header to select the column.

  • Press SHIFT and click to select consecutive columns.

  • Press CMD/CTRL to select multiple discrete columns.

A set of suggestion cards is displayed on the right side that reflect the column or columns that you have selected.

Column menu

When you select a single column, you can explore additional transformations through the column menu.

CS-TransformColumns-ColumnMenu.png

Figure: Column menu

The options available in the column menu depend upon the currently selected data type of the column. For example, the options for Integer columns are different than the options for String columns.

Tip

The displayed column menu applies only to the column from which it was opened. You cannot apply column menu transformations to multiple columns, although you can modify the transformation after it has been added to your recipe to apply to multiple columns.

Cut, Copy and Paste Columns

Steps:

  1. Click the Columns icon in the Transformer toolbar.

  2. In Column Browser, select the header of the column.

  3. From the column menu, select Cut or Copy.

  4. Select the column where you wish to paste the column.

  5. Right-click and select the appropriate Paste option: before, after, or paste values.

Move Columns

You can move one or more columns to a new location in your dataset.

Steps:

  1. Click the Columns icon in the Transformer toolbar.

  2. In Column Browser, select the column or columns to move.

  3. Right-click and select the appropriate Move option:

    1. To beginning: Columns are moved to the beginning of columns in the dataset.

    2. To end: Columns are moved to the end of columns in the dataset.

    3. Before/after: In the Transform Builder, you can specify exactly where you wish to move the columns.

  4. When the transformation is added to your recipe, the columns are moved.

Rename Columns

You can rename one or more columns in a single step.

Steps:

  1. In the Transformer toolbar, click the Grid icon.

  2. In the data grid, click the name of the column.

  3. In the data grid, select Renamefrom the column menu.

  4. Change the name as needed.

  5. In the Transform Builder, you can add additional columns to be renamed.

    1. Click + Add.

    2. Enter the name of a column to rename.

    3. Enter the new name.

    4. Repeat as needed.

      CS-TransformColumns-RenameColumns.png

      Figure: Rename multiple columns

  6. When the columns have been renamed, click Add to add the step to your recipe.

Change Column Data Type

By default, Designer Cloud attempts to infer a column's data type based on the data contained in it.

A data type defines the expected values in a column. For example, when Designer Cloud detects numeric values in a column, the column may be assigned the Integer data type or the Decimal data type, depending on the data in it.

In some cases, you may need to re-assign a column's data type. For example, if you are modifying values and sub-values in the column, you may find it easier to work with the data temporarily setting it to String data type.

Tip

To change the data type of multiple columns to the same type, you may find it easier to use the Column Browser. Select the columns. Then, right-click the select and select the appropriate type from the Change type menu.

Steps:

  1. If you are changing the data type for a single column, select the column header.

  2. From the data type menu, select the new type:

    CS-TransformColumns-ChangeDataType.png

    Figure: Change data type

  3. A step is added to your recipe to change the selected column to the new type.

    Tip

    You can edit the recipe step to apply the new data type to more columns.

Edit Column

You can modify the contents of a column through transformation.

Steps:

  1. Select the column. From the column menu, select Edit with formula.

  2. The Edit with Formula transformation is displayed in the Transform Builder:

    CS-TransformColumns-EditWithFormula.png

    Figure: Edit with formula

    1. Formula: Enter a valid formula. You can edit functions, column references, constants, or combinations of these objects.

    2. Group rows by: The formula can be calculated based on the groupings of values of a selected column. For example, you may wish to compute based on separate values in the Region column.

  3. To edit the column with the new formula, click Add.

Delete Columns

Select the column or columns. From the column menu, select Delete.

Tip

To restore the columns, you can click the Undo icon immediately in the Transformer toolbar, or you can delete the recipe step through the Recipe panel.

Merge Columns

You can concatenate non-numeric data together from multiple columns into a new column. You can apply this technique to columns of different un-nested data types.

Steps:

  1. In the Transformer page, identify the columns that you wish to merge.

  2. Click theMerge columnsicon in the toolbar.

    Tip

    You can also search for Merge columns in the Search panel.

  3. In the Merge columns transformation, enter the required details:

    • Columns: The columns containing the values you wish to merge. You can select from the following options:

      • Multiple: Select one or more columns from the drop-down list.

      • Range: Specify a start column and ending column. All columns inclusive are selected.

      • All: Select all columns in the dataset.

        Note

        This option merges the data in all of the columns into a new single column.

      • Advanced: Specify the columns using a comma-separated list. You can combine multiple and range options under Advanced. Example:

        c1,c3,c5~c8

        Above specifies the following columns: c1,c3,c5,c6,c7,c8.

    • Separator: The separator to be placed between each value. This value can be empty.

    • New column name: The name of the new column.

  4. To add the step to your recipe, click Add.

  5. The new column is added to your dataset.

  6. As needed, you can delete the source columns.

In this section: