---
schema_version: "1"
id: 4254db4bc83d9863ec7cafa1c26d501a9e5d4e05bdac3adf64ded04ad7229666
title: Transform Fields in a Recipe
collection: admin
version: current
locale: en-us
portal_url: https://docs.salesforce.com/admin/current/en-us/analytics/264-0-0/bi_integrate_data_prep_recipe_transform_fields.md
canonical_url: https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_data_prep_recipe_transform_fields.htm&release=264.0.0&type=5
source_site: help.salesforce.com
content_type: documentation
content_hash: be58176cafb4be183e814aacb9855de3a5a0cbd31e74e61f40d8883c63f26c5a
description: Data is not always consistent, especially when you combine data from different sources. In a recipe, you can transform fields to ensure that values are consistent in your target dataset. Change case, split values to get just the parts you need, and replace incorrect values.
products:
  - CRM Analytics
  - Cross Cloud
release: "264"
---
# Transform Fields in a Recipe

Data is not always consistent, especially when you combine data from different sources. In a recipe, you can transform fields to ensure that values are consistent in your target dataset. Change case, split values to get just the parts you need, and replace incorrect values.

![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_note.png)

Note To transform column values in a Data Prep recipe, see [Formula Transformation: Create a Calculated Column Based on an Expression](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_recipe_transformation_formula.htm&release=264.0.0&type=5 "In CRM Analytics, create a column in a Data Prep recipe that displays values based on a formula calculation. The calculation can include input from other fields in the same row or across rows. For example, you can create a Profit column based on input from Revenue and Cost columns. Enter formulas in EA-SQL format. EA-SQL is a collection of standard and custom functions for numeric, string, and date data."). To accomplish these tasks in a dataflow, see [computeExpression Transformation](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_saql_transformation.htm&release=264.0.0&type=5 "The computeExpression transformation enables you to add derived fields to a dataset. The values for derived fields aren’t extracted from the input data source. Instead, CRM Analytics generates the values using a SAQL expression, which can be based on one or more fields from the input data or other derived fields. For example, you can use an expression to assign a value to a field, concatenate text fields, or perform mathematical calculations on numeric fields.").

When you transform a field, a new field is added with the new values to the right of the original field. You can choose to keep or remove the original field.

To transform a field, click ![Column menu button](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/resources-global/analytics/images/bi_integrate_replication_actions_button.png) in the column header (1), and then click the transformation that you want to use. Alternatively, select a transformation from the Suggestions bar at the bottom of the recipe.

Transformation settings appear in the Add Transformation panel below the preview data.

![Transform fields](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/bi/images/bi/bi_integrate_data_prep_recipe_transform_fields.png)

You can apply these field transforms.

<table class="slds-table slds-table_bordered slds-max-medium-table_stacked slds-m-bottom_small ">
      <colgroup>
        <col style="width:27.397260273972602%">
        <col style="width:28.21917808219178%">
        <col style="width:44.38356164383562%">
      </colgroup>
      <thead class="slds-text-title_caps ">
        <tr class="">
          <th scope="col" class="slds-cell-wrap" style="vertical-align:top;" id="d22190e79">Transform</th>
          <th scope="col" class="slds-cell-wrap" style="vertical-align:top;" id="d22190e82">Description</th>
          <th scope="col" class="slds-cell-wrap" style="vertical-align:top;" id="d22190e85">Example</th>
        </tr>
      </thead>
      <tbody>
        <tr class="">
          <td data-label="Transform" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e79 ">Extract</td>

          <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e82 ">Extract the selected component from a date field into a new field.</td>

          <td data-label="Example" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e85 ">Extract the hour component from the case created date to analyze case creation by
            hour of the day.</td>

        </tr>
        <tr class="">
          <td data-label="Transform" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e79 ">Lowercase</td>

          <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e82 ">Convert values in selected field to lowercase.</td>

          <td data-label="Example" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e85 ">
            <p>Correct lead email addresses captured at a trade
              show.</p>
            <p style="margin-bottom:0;"><kbd>JSMITH@FORRESTER.COM</kbd> becomes
              <kbd>jsmith@forrester.com</kbd>.
            </p>
          </td>

        </tr>
        <tr class="">
          <td data-label="Transform" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e79 ">Replace</td>

          <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e82 ">
            <p>Find a specific value in a field and replace it with a new value. A new field is
              created containing both replaced and unchanged values.</p>
            <p style="margin-bottom:0;">You can also replace nulls
              with a value. For example, to allow users to group transactions that don’t have a
              product, replace all nulls in the Product field with “N/A”. </p>
          </td>

          <td data-label="Example" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e85 ">
            <p>Make country values consistent.</p>
            <p style="margin-bottom:0;"><strong class="uicontrol">Find</strong> all instances of
              <kbd>US</kbd> and <strong class="uicontrol">Replace</strong> them with
              <kbd>USA</kbd>.
            </p>
          </td>

        </tr>
        <tr class="">
          <td data-label="Transform" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e79 ">Split</td>

          <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e82 ">Divide values into multiple parts at the delimiter and create a field for each
            part.</td>

          <td data-label="Example" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e85 ">
            <p>Divide phone numbers into area code and number.</p>
            <p style="margin-bottom:0;">Select the
              <kbd>-</kbd> delimiter to split <kbd>925-900 2123</kbd>
              into two new values <kbd>925</kbd> and <kbd>900
                2123</kbd>.
            </p>
          </td>

        </tr>
        <tr class="">
          <td data-label="Transform" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e79 ">Substring</td>

          <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e82 ">Starting at the character <strong class="uicontrol">Position</strong> in a value, extract the
            number of characters in <strong class="uicontrol">Length</strong> into a new field.</td>

          <td data-label="Example" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e85 ">
            <p>Extract the month from date values.</p>
            <p style="margin-bottom:0;">Enter a <strong class="uicontrol">Position</strong> of
              <kbd>4</kbd> and a <strong class="uicontrol">Length</strong> of
              <kbd>2</kbd> to extract the month value <kbd>10</kbd> from
              the month value <kbd>2016-10-23</kbd>.
            </p>
          </td>

        </tr>
        <tr class="">
          <td data-label="Transform" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e79 ">Trim</td>

          <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e82 ">Remove leading and trailing whitespaces.</td>

          <td data-label="Example" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e85 ">
            <p>Remove whitespaces to ensure that values from one source are consistent with
              values from another source.</p>
            <p style="margin-bottom:0;"><kbd>“ CA “</kbd> becomes
              <kbd>“CA”</kbd>.
            </p>
          </td>

        </tr>
        <tr class="">
          <td data-label="Transform" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e79 ">Uppercase</td>

          <td data-label="Description" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e82 ">Convert values in selected field to uppercase.</td>

          <td data-label="Example" class="slds-cell-wrap" style="vertical-align:top;" headers="d22190e85 ">
            <p>Correct US state values.</p>
            <p style="margin-bottom:0;"><kbd>ca</kbd> becomes
              <kbd>CA</kbd>.
            </p>
          </td>

        </tr>
      </tbody>
    </table>

![Important](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-analytics-bi-264-0-0-production-enus/ec791473-f5b4-4f64-a656-9104f455d966/images/icon_important.png)

Important CRM Analytics datasets contain date and time component fields, such as Year, Quarter, and Month, that are derived automatically from existing date and time fields. Transformations on these component fields are not supported.

#### See Also

- [Standardize Date Formats](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_data_prep_date_format.htm&release=264.0.0&type=5 "If a dimension field contains dates in different formats, use the Format Dates recipe transformation to standardize the format for all values in the field. A consistent format enables you to correctly filter and group records by date, including filtering by date component, such as month. It also ensures that you can successfully convert the field type from dimension to date.")
- [Convert Field Types in a Recipe](https://help.salesforce.com/s/articleView?id=analytics.bi_integrate_data_prep_recipe_convert_field_types.htm&release=264.0.0&type=5 "The type assigned to a dataset field determines how you can query that field’s data. For example, you can filter and group by a dimension or date field, or perform math calculations on a measure field. When you load data into a dataset, CRM Analytics sometimes tags a dataset field with the wrong type. If needed, convert fields to the correct types.")