Transform by Example powered by GenAI

Zoho DataPrep helps you transform columns based on example inputs using GenAI, powered by OpenAI or Zoho LLM. To enable this feature in your DataPrep organization, go to the GenAI Integration tab on the Settings page. Click here to learn more about enabling OpenAI integrated features.

Here's a quick video on integrating the ChatGPT API with Zoho DataPrep.

Transform by example easily helps you transform the data in any column just by providing examples of the output. Using this feature, you can provide examples of the desired output for a few cell values in your data column. Zoho DataPrep uses GenAI to automatically generate the corresponding output for the remaining values.

Here's a quick demo on the features powered by OpenAI in Zoho DataPrep.

The prompt and required contextual data will be shared with the selected AI vendor. Personally identifiable information (PII) is never shared with the selected AI Vendor.

The GenAI features, when enabled, can be used by the Account admin and Organization admins in your DataPrep organization.

The API key is specific to the DataPrep organization it is configured in and will not be shared across organizations or with any other Zoho service.

To configure and enable OpenAI's ChatGPT integration

OpenAI in Zoho DataPrep works in a BYOK (Bring Your Own Key) model, in which users bring their own ChatGPT API Key and OpenAI Organization ID. If you are already an OpenAI user, you can use your API key and organization ID to unlock AI features in DataPrep.

Data Privacy Notice: Zoho DataPrep utilizes OpenAI's ChatGPT API, an external service based in the USA, subject to OpenAI's Terms of Use. Please be aware that the information sent to OpenAI depends on the access provided by you, the user. The data shared with ChatGPT is based on the features you enable. Details such as column names and/or selected cell data will be shared based on each feature. You can choose to enable or disable each feature based on the level of data you want to share. Therefore, exercise caution while using this service and ensure that the data you provide does not contain confidential or personal information.

Notes:

The generative responses are coming in from ChatGPT's training data. Accuracy, consistency, and relevance of the response are subjective to the type of prompts and the data available in its training set.
ChatGPT doesn't respond to prompts about personal information.

1. Right-click on a column, select the Transform by example option from the context menu. The GenAI Configuration tab will open. Click Authenticate Now to authenticate with the selected AI vendor.

The GenAI Configuration tab opens only for the first time. From then on, you can edit the configuration from the GenAI Configuration tab on the Settings page.

Note: The GenAI configuration on DataPrep's Settings page will be visible only to the Account admin.

Only the Account admin can enable features for your DataPrep organization.

2. Click Authenticate now next to OpenAI.

3. A consent pop-up will be shown. Please read the data privacy notice carefully before you proceed. By proceeding, you acknowledge and accept the risks and OpenAI's terms of use policy.

4. Provide the API Key and Organization ID of your OpenAI organization to configure OpenAI integration in your DataPrep organization.

Note: The key will be encrypted and securely stored in Zoho DataPrep. Learn how to get the Organization ID and the API key from OpenAI

5. Once saved, go to the GenAI Skills tab. Toggle on Transform by Example, and select OpenAI from the vendor dropdown.

Organization ID and API key in OpenAI

1. Go to your settings page in OpenAI to get the Organization name and ID. Here's a snapshot for reference. Read more from OpenAI

2. Go to the API keys tab under the Organization section to get your API Key. Here's a snapshot for reference. Read more from OpenAI

To configure and enable Zoho LLM

Zoho LLM is Zoho's own large language model. You can enable it directly from the GenAI configuration settings without needing an external API key. Once enabled, it powers AI features in Zoho DataPrep.

Note: This setting is visible and accessible only to the Account admin.

The GenAI Configuration tab opens only for the first time. From then on, you can edit the configuration from the GenAI Configuration tab on the Settings page.

2. Locate Zoho LLM in the AI Vendor list and click Enable configuration.

3. A consent pop-up will be shown. Please read the data privacy notice carefully before you proceed.

4. In the Authentication panel, confirm that the Zoho LLM toggle is enabled and click Save.

5. Once saved, go to the GenAI Skills tab. Toggle on Transform by Example, and select Zoho LLM from the vendor dropdown.

Transform by Example - powered by GenAI

1. Right-click on a column and select the Transform by example option from the context menu.

2. An editable version of the selected column appears right next to the original column, where you can type in the required output against a few cell values from your data column as an example, and press Enter.

3. Once you enter the example, the rest of the column data is transformed, and the output column name is contextually auto-generated by GenAI based on your input prompt and shown as a preview. The formula script used to get the output is displayed in the Generated formula panel.

Transform by example is now equipped with a retry mechanism that gets you the best result out of GenAI's response by retrying the query automatically in a different way if the output data did not match your expectation.

4. For instance, I have given the domain name "zylker.com" here in letters as an example, and the transform automatically derives the rest of the output using the auto-generated formula powered by GenAI.

5. If the generated output doesn't match your requirements for all the values, you can provide another example and press Enter. DataPrep now takes the new input into consideration and generates a new way to transform the data.

6. The updated formula will also be displayed in the Generated formula field in the right panel.

5. Click the Apply to apply the transform.

Notes:

1. You can also modify the generated formula using the Customize option.

2. This converts the transform by example panel into a formula column transform. You can now use the full capability of the formula column feature to fine-tune the formula to your requirements.

3. You can also use the chat-based formula builder powered by OpenAI ChatGPT to generate the required formula. Click here to learn more.

Here are some example use cases for you to try out Transform by Example:

a) I want to change the format of the values in the date column below to the format that represents the day along with the date. I just type in the example for the first value with "Thursday, 06 December 2018", and DataPrep fills the rest of the column with the required output. Here's the snapshot of the generated formula and the output.

b) I want to extract the names from the Email ID column. I just type in the value "john" as an example, and DataPrep fills the rest of the column with the required output. Here's the snapshot of the generated formula and the output.

c) Here, I have given the first example "Donnie Denson" to separate the names with a space. Below is the snapshot of the auto-generated formula and the output for the rest of the column.

d) I wish to clean the phone numbers in my dataset by removing the delimiters and standardizing them. Below is the snapshot of the auto-generated formula and the output for the rest of the column.

Generate formula using OpenAI ChatGPT

Use ChatGPT to find new datasets