Data transformations provide powerful building blocks for improving and simplifying your data. Bytespree Studio provides four built-in data transformation tools to help users get more out of their data in less time.

Here's how you access transformations in Bytespree Studio

First, select the column in which you'd like to transform data. Next, click the "View and manage transformations" column control (pictured below). Finally, click + Add a transformation.

Understanding the four types of transformations

Find and Replace

Find and replace is a powerful (sometimes too powerful) transformation. Let's say you wanted to find any instances of the text welocme with welcome in a field called preferred_greeting. Find and replace is the right tool for the job.

But let's say, for instance, that you have a field called state and want to convert all abbreviated state names to their fully spelled equivalents. Find and replace could have unintended consequences because rules are executed in sequential order. Here's a scenario which explains why:

Convert MD to MARYLAND

Convert MA to MASSACHUSETTS <<< This rule would correctly convert MA to MASSACHUSETTS but would also convert MARYLAND to MASSACHUSETTSYLAND. Get the idea? The If/Then transformation type is a much better solution for this particular purpose.

If/Then

If/then transformations allow you to create a logical expression that then dictates a column's value. For example, let's say we have a column called country, and we're trying to determine which accounts are either "foreign" or "domestic". We could build out multiple If/then transformation as follows:

If country is equal to United States then country is domestic

If country is equal to United States of America then country is domestic

If country is equal to America then country is domestic

If country is equal to USA then country is domestic

If country is equal to US then country is domestic

If country is not equal to domestic then country is foreign

Notice in this example that our final condition relies on the application of our previous conditions.

Change Case

Changing case is the most straightforward of all conditions but can be particularly helpful in data cleanup. For example, let's say we're trying to standardize data in column state which has the following values:


California

california

ca

CA

CALIFORNIA

If our objective is to get each state into it's abbreviated form, we could write just two transformations to fix every value as follows:

Change case to uppercase

If state is equal to CALIFORNIA then state is CA

If we had not applied the uppercase transformation first, then we'd be required to write three separate if/then transformations for California.

Cast to type

The cast to type transformation tells Bytespree Studio to treat a particular column as a certain type of data. Depending on the data type, different functionality is available in Bytespree. To determine a column's data type, hover over the column name and wait for the tooltip to appear.

  • Date columns should read Type - date

  • Numeric columns should read either Type - decimal, Type - numeric, Type - float, Type - int, or Type - bigint

  • Timestamp columns should read Type - timestamp

If your column reads character varying, varchar, or text, you won't have numeric sort/filter capabilities for numbers of date-and-time sort/filter capabilities for dates. To resolve this issue, apply the appropriate transformation to the column.

Did this answer your question?