Transform data

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).

Suggest transformations

The Suggest option 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:

• 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, i.e. Concatenate columns 1,5&9 together or check that ‘End date’ is always greater than ‘Start date’.

Learn more about Functions

Using a View on top of your data

Creating a View, or multiple Views, on top of your data allows you to transform it (remove columns, filter rows, obfuscate values, etc.) without making changes to the Dataset. Unlike Datasets, Views can be shared across Spaces, so you could create a View of your data relevant to your Data analysts and another for your Data scientists who have access to different Spaces.

Learn more about Views