Transform data

Data Studio offers a number of ways to alter your data.

Combine one or more datasets together. This is done using a Workflow with a Source step for each table of data (Dataset or View) being combined, then one of the Workflow steps depending on how you want to merge the data:

• Join – like a database join operation using a logical relationship(s) between columns in both tables.
• Lookup - lookup values in a reference dataset to return corresponding values.
• Splice - combine two inputs presenting the tables next to each other.
• Union - combine multiple inputs presenting the tables on top of each other.

Working with a single table of data you can, when exploring your data, apply actions or corresponding Workflow steps:

• Chart – visualize your data.
• Filter/Split - apply a true/false filter like matches/contains/greater/less than to split into passing and failing rows.
• Group – can dedupe values across chosen columns and aggregate including a Count, Sum, Max, Min etc. of all the values in the column.
• Sample – reduce the number of rows or take a random sample from a larger dataset.
• Sort - arrange data in ascending or descending order.
• Transform - Manipulate and configure your data.

Transforming column layout and order

Column order can be changed, columns can be renamed, excluded/included or duplicated/copied.

Transform or Map to target?

The Map to target Workflow step is useful for larger changes when updating the entire schema/layout of your data.

Suggest transformations

The Suggest option is shown on the Transform configuration page. It will analyze the values in the data table being explored and return recommended Functions that will improve the consistency of the data. Some examples are Trim and Compact spaces to remove unnecessary space characters or convert null to zero for numeric columns contains both. Also Hash, which will obfuscate sensitive data before it is saved as a View and shared.

Selecting the Functions to apply to each column and optionally New column if the column should be duplicated before the Function is applied leaving the existing value unedited.

Transforming column values and creating new columns

A column(s) can be transformed by applying one or many Functions to it. This transformation can change the values in the existing column or create a new column containing the result without changing the existing value(s).

There are hundreds of Native Functions which can be combined to make up complex operations. Most commonly used Function categories are:

• Text Functions to manipulate Uppercase, Lowercase, Remove noise, etc. alphanumeric values.
• Math and Number Functions to Add, Subtract, Round, etc. numbers.
• Date & time Functions to compare, add, format, etc. dates.
• Validation Functions to check values Equals, Contains, Greater, Less than or Format is as expected, returning either true or false.

More complex Custom Functions can be built out to look at multiple columns. For example, concatenate columns 1,5&9 together or check that End date is always greater than Start date.

Using Views

Creating separate Views of your data allows you to transform it (e.g. remove columns, filter rows, obfuscate values) without making changes to the Dataset.

Unlike Datasets though, Views can be shared across Spaces. This lets you create separate Views for different users or user groups. For example, you can create one revant to your Data analysts and another one for Data scientists.