Performing a Data transformation in Cells, rows or Columns

To transform your data, you can use the Context-Sensitive Editing Features and the Transform Bar. When you select a column, a cell, or a content in a cell, a menu appears with options to perform transforms:
  • Quick Actions: allows to perform actions such as remove duplicate columns, hide columns, or delete columns or rows. The table below lists all the available actions:
    Action What it does...
    Delete Rows Delete rows in the data, either by selecting individual members, or by specifying a range (not possible for text columns; only numerical and Date columns).
    Duplicate Column Create a copy of an existing column.
    Delete Column Delete a column. Use the Shift key to select and then delete multiple columns.
  • Create a Transform: lists suggested transformations to apply to the column, such as replacing the value in a cell with a suggested value. You can also select Create a Transform and choose from the options listed under the transformation bar displayed above the grid. The transformation bar is used to edit and create transforms.
    As you hover over a suggested transformation, the anticipated results are previewed in the grid. To apply, simply select the transform. You can manually define your transformation in the transformation bar; as the transform is built, a preview is provided in the affected column, cell, or content within the cell. The table below lists the available transformations you can apply to selected columns, cells, or content within cells.
    Transformation Description Transform Bar Format
    Remove duplicate rows Remove all duplicate rows when creating or adding data to a model across all columns of a dataset. Available only as a task bar icon
    Concatenate Combine two or more columns into one. An optional value can be entered to separate the column values. Concatenate [<Column1>], [<Column2>]… using "value"
    Split Split a text column on a chosen delimiter, starting from left to right. The number of splits can be chosen by the user. Split [<Column>] on "delimiter" repeat "#"
    Extract Use this transform to extract a block of text specified as numbers, words, or targeted values within a column to a new column. Extract [<what to extract]>] [<where to extract>] [<which occurrence>] [""] from [<column name>] [<include value option> ].

    Options for what to extract:

    • number Limits extracted text to one number from column cell.
    • word Limits extracted text to a word from column cell.
    • everything Includes all text.
    Options for where to extract relative to the target value:
    • before
    • after
    • between
      Note
      You must specify two target values when using between.
    • containing: Extracts a word or number containing the target. For example, if your target is ship, both ship shipping will be extracted.
    • equal to: Extracts the specific target value if it exists in the cell.
    Options for specifying occurrence:
    • first
    • last
    • occurrence: Allows you to specify the position of the target from one to ten.
      Note
      Use occurrence when there are multiple instances of the target.

    To extract the first number from all column cells, you would specify the following:

    Extract before first "" from [<column name>].

    To extract all text between parenthesis in column cells, you would specify:

    Extract everything between "(" and ")" from [<column name>].
    Change Change a column to uppercase, lowercase, or title case. Change [<Column>] to (<UPPERCASE>/<lowercase>/<TitleCase>)
    Replace Replaces either an entire cell or content that could be found in multiple different cells.
    Note
    You can optionally select Start of the navigation path Next navigation step whereEnd of the navigation path to add a <where> clause to the transform bar. You need to specify an associated column and value when using a <where> clause to limit the replace action in a given column.
    		 Replace (<cell/content>) in [<Column>] matching "value" with "value"

    With a <where> clause: Replace (<cell/content>) in [<Column>] matching "value" with "value" where[<Column>]is "value"
    Filter Filter a column to include or exclude values or date ranges. Filter [<Column>] Matching, Not Matching, Between value.
    Note
    For Between a date range is required.