How to transform and derive columns in DataLens
Turn the columns you were given into the columns you need — types fixed, names consistent, and new values calculated from the old ones.
What you will do
You will reshape a dataset's columns: fix types, split composite fields, standardise naming and values, aggregate, and build new columns from expressions.
Like cleaning, each transform is a recorded step on a working copy, so the sequence is repeatable and inspectable.
When this is useful
- A date arrived as text and nothing will chart until it is a date.
- One column holds two things — "Smith, John" — and you need them apart.
- Column names came from three systems with three conventions.
- You need a metric the source does not have: margin, days since signup, a percentile rank.
Before you start
- A loaded dataset — Ideally cleaned first — transforms are easier to reason about once the nulls and duplicates are dealt with.
- Knowing what you want to end up with — Transform steps are quick to apply; knowing the target shape saves applying six and undoing four.
Steps
Open Prepare → Clean & Transform and pick the Transform group
In Prepare → Clean & Transform, the operations are grouped as Filter Rows, Clean, Transform and Derive.
Transform holds the column-reshaping operations; Derive holds the ones that create new columns.
The four operation groups, with Transform showing its operations.
Fix column types
Cast Type converts a column from one type to another — text to number, text to date.
This is the fix for the single most common problem in loaded data: a column that is numeric or a date in meaning but text in storage, because one row had a stray value.
The preview showing the column with its new type.
Tidy text and names
Normalise Text cleans up casing and spacing in a text column. Regex Replace handles patterned substitutions where a simple rule is not enough.
Rename Column changes one column name. Standardise All Names applies a Naming Convention across every column at once — the right move when three sources have three conventions.
Standardise Values does the equivalent for the contents rather than the headers, which is what CRM country and status fields usually need.
Split a composite column
Split Column separates one column into several on a delimiter — a full name into first and last, a code into its parts.
The preview shows the resulting columns before you apply, which is how you catch a delimiter that appears twice in some rows.
New columns in the preview, one per split part.
Combine datasets where you need to
Append / Union stacks another dataset underneath this one — the right operation for twelve monthly files that share a schema.
Lookup Enrichment brings values in from a Reference Table using a Join Key, for the case where you need to enrich rather than stack.
Derive new columns
Derived Column creates a new column from an expression. Use Build Expression to construct it rather than typing it blind.
Group By / Aggregate collapses rows into summary rows. Z-Score Column and Percentile Rank add statistical positioning to a numeric column — useful when "high" needs a definition rather than an opinion.
The new column in the Live Data Preview with its computed values.
Save the shaped dataset as a view
Use Save as View to name the result. It becomes selectable in Model, Analyse and Deliver.
What happens next
A shaped dataset is what modelling wants. Take it into Model to define target structures, or into Analyse to chart it now that the types are right.
If you find yourself applying the same sequence repeatedly, Presets in Prepare save a set of steps for reuse.
Example
A dataset has a full_name column and a signup_date stored as text. The analyst casts signup_date to a date, splits full_name on the comma into last and first, standardises all column names to one convention, and derives days_since_signup. Four steps, all recorded, and the dataset finally charts.
Tips
- Cast types before deriving anything. An expression over a text column that should be a number will not do what you expect.
- Use Standardise All Names once rather than renaming columns one at a time across a wide dataset.
- Build Expression rather than typing an expression from memory — it shows you what is available.
- Preview a split before applying it. Delimiters that appear twice in a minority of rows are the usual surprise.
- Save a preset when a sequence will be used again on next month's file.
Limitations
- Transforms operate on a working copy of the dataset, not on the source system.
- Append / Union expects compatible schemas. Stacking datasets whose columns differ substantially is not the operation you want.
- Derived columns are built with the expression builder provided; arbitrary code is not executed here.
- The available operations are the 22 in the operation list. Anything outside them belongs in a modelling or pipeline step instead.
Related how-to guides
Clean a messy dataset
Drop nulls, fill gaps, remove duplicates and cap outliers — with a live preview before anything is applied.
Join datasets and create views
Join datasets on the keys that relate them and save the result as a reusable view.
Chart and pivot a dataset
Bar, line and scatter charts, histograms, a correlation matrix and a pivot table over your data.
Build a data pipeline
Turn a sequence of steps into a repeatable pipeline with a defined execution order.
Related questions
More of these on the DataLens FAQ page.
Can I create calculated columns in DataLens?
Yes. Derived Column in Prepare builds a new column from an expression, using the Build Expression helper. Z-Score Column and Percentile Rank add statistical positioning to a numeric column, and Group By / Aggregate produces summary rows.
How do I fix a column that loaded as text but should be a number or a date?
Use Cast Type in Prepare → Clean & Transform. It converts the column and shows the result in the live preview first, so you can see whether every row converted before applying it.
Can I combine several files with the same structure?
Yes. Append / Union stacks one dataset underneath another where the schemas are compatible — the usual approach for a set of monthly extracts that share a layout.
Try this in DataLens
DataLens is in private beta. Request access and work through this guide on your own data.
Request beta access