Apply Data Transforms to Cells, Rows, and Columns in Datasets

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.

Create Your Own Transformations

Open the Custom Expression Editor and access the Wrangling Expression Language (WEL) to define your own transformations using predefined functions and a scripting editor for specific wrangling capabilities.

Once your dataset is created, you need to model your data for further needs. SAP Analytics cloud offers a large range of tools to help you transforming your data. However, it might be that the available transforms do not cover all your needs. In such cases, accessing the Wrangling Expression Language and define your own transformations using this language tool is the solution:

  1. Open the Custom Expression Editor located in the Action toolbar.

  2. Type in a custom expression directly or use keyboard shortcuts to build an expression.
Note
Refer the Custom Expression Help panel to get detailed description, syntax, and examples for each of the available functions.
The expressions are grouped into 5 types:
  • Numeric Functions let you work with numerical values.
    Example
    Your dataset contains information on 30 years and you want to group the years into bin of 5 years. Use binByBinWidth : [column] = binByBinWidth([Year],5).
  • String Functions let you work with text values.
    Example
    You have a large dataset containing data on key social and economic indicators in the United States. You want to see only the tax indicator: Use startsWith and filter on strings starting with "Tax":[column] = startsWith(indicator Name, 'Tax').
  • Date & Time Functions let you work with date and time values.
    Example
    You want to calculate the difference in months between two dates. Use dateDiff: [column] = dateDiff(2016, 2020, 'month').
  • Spatial Functions let you work with spatial values.
    Example
    You want to calculate the distance between two locations, based on specified latitudes and longitudes. Use distance: [column] = distance(latitude_1, longitude_1, latitude_2, longitude_2).
  • Other Functions
    Example
    You want to add unique row numbers to each row of your dataset, which can be used to see individual measures instead of aggregated ones in your story. Use rowNumber: [Row Number] = rowNumber(). A new dimension containing the row numbers is added to your dataset.
    Note
    Row number can be different between sample and full data or depending on data refreshes. Moreover, if later you use a transformation that duplicates rows, this can result in duplication of row numbers as well.
Each time you create a custom expression, a transform log is created. Using this log, you can edit your expression as many times as you want.
Note
You access the transform log clicking the button Transform Log.