How to clean a messy dataset in DataLens
Cleaning is where most data work actually goes. Do it as a recorded sequence of steps with a live preview, rather than as edits nobody can retrace.
What you will do
You will apply cleaning operations to a loaded dataset — dropping or filling nulls, removing duplicates, handling outliers — and see the effect of each one before committing to it.
Every operation is a recorded step, not an edit. The original data stays intact, and anyone can see later exactly what was done to it.
When this is useful
- A file has missing values and you need to decide what to do about them.
- A dataset has duplicate rows from a bad export or an overlapping extract.
- Extreme values are distorting an average or a chart.
- You need to hand someone a clean dataset and be able to say precisely how it was cleaned.
Before you start
- A loaded dataset — Anything from Data → Connections — an upload, a database table, an API response.
- Some idea of what is wrong — The dataset summary and the profiling screen in Govern both tell you where the nulls and the odd values are.
Steps
Open Prepare → Clean & Transform
Select Prepare in the navigation, then Clean & Transform.
Choose the dataset you want to work on. The screen shows a Live Data Preview of its current state.
Your data in a preview table, and an operation list grouped into Filter Rows, Clean, Transform and Derive.
Deal with missing values
Drop Null Rows removes rows where a chosen column is empty. Use it when a row without that value is meaningless.
Fill Nulls replaces them instead. Choose a Fill Strategy: Mean, Median, Mode (most common), or a custom value.
The choice matters. Filling a numeric gap with the mean keeps the row but moves the average; dropping it keeps the average honest but loses the row. Decide which cost you would rather pay.
The preview updates to show the effect before you commit.
Remove duplicate rows
Drop Duplicates removes repeated rows. Choose which columns define a duplicate — the whole row, or a key such as customer_id.
Choosing the key columns matters more than it looks. Two orders from the same customer are not duplicates; two rows with the same order_id are.
The row count in the preview drops by the number of duplicates removed.
Handle outliers
Cap Outliers pulls extreme values back to a boundary, keeping the row. Remove Outlier Rows deletes them entirely.
Cap when the row still carries useful information in its other columns. Remove when the value is clearly an error rather than an extreme.
Drop what you do not need
Drop Column removes columns from the working dataset. A narrower dataset is faster to work with and easier to read.
Use Sample Dataset if you want to work against a subset while designing the sequence — Random sample, Bottom N rows, or First 5 rows.
Check the preview, then apply
The Live Preview shows the result of the step before it is applied. Read it. Catching a mistake here costs a second; catching it three steps later costs the sequence.
Apply when the preview is right. The step joins the recorded sequence for this dataset.
The step listed in the applied sequence, and the preview reflecting it.
Save the result as a view
Use Save as View and give it a name. The cleaned result becomes something the rest of the platform can use — Model, Analyse and Deliver all see it.
The original dataset is unchanged and still available.
The named view available for selection elsewhere in the platform.
What happens next
A cleaned view can be modelled, charted, joined or published like any dataset. Prepare → Clean & Transform is also where you would go next for transforms — casting types, splitting columns, deriving new ones.
The cleaning sequence itself is visible in Govern → Lineage, which shows the source, each transform step, and the current staged state.
Example
A sales extract has 12,000 rows, 400 with no region and a handful of orders with a value of 9,999,999 from a test system. The analyst drops the null-region rows, removes the test orders as outlier rows, drops duplicates on order_id, and saves the result as "Sales cleaned". The average order value is now believable, and the four steps that made it so are recorded.
Tips
- Read the Live Preview before every apply. It is the cheapest error check in the product.
- Choose duplicate keys deliberately. Whole-row deduplication misses duplicates that differ in a timestamp.
- Prefer Cap Outliers to Remove Outlier Rows when the row has other useful columns — capping keeps the record.
- Save intermediate views at meaningful points. Coming back to a half-cleaned state beats redoing eight steps.
- Profile in Govern first if you are not sure what is wrong. It will tell you where the nulls and patterns are before you start guessing.
Limitations
- Cleaning applies to a working copy. Your source file, table or API remains untouched — this is by design, but it does mean the source is not fixed by cleaning here.
- Operations apply to a dataset. Cleaning two datasets means running the sequence on each.
- Fill Nulls strategies are Mean, Median, Mode and a custom value. Model-based imputation is not among them.
- Outlier handling uses the operations provided. Custom statistical rules beyond those are not configurable here.
Related how-to guides
Transform and derive columns
Cast types, split and rename columns, standardise names and values, and build new columns from expressions.
Profile a dataset and find PII
See what is really in each column, and find the personal data before it ends up somewhere it should not.
Join datasets and create views
Join datasets on the keys that relate them and save the result as a reusable view.
Upload a CSV or Excel file
Load a spreadsheet or delimited file and get a profiled dataset you can clean, model and chart.
Related questions
More of these on the DataLens FAQ page.
Does cleaning data in DataLens change my original file or table?
No. Cleaning operations apply to a working copy and are recorded as steps. Your uploaded file, database table or API source is never modified, and the original dataset stays available alongside the cleaned view.
Can I see what cleaning steps were applied to a dataset?
Yes. Every operation is recorded as a step rather than an edit, and Govern → Lineage shows the source, each transform in order, and the current staged state.
How does DataLens handle missing values?
Two ways. Drop Null Rows removes rows where a chosen column is empty. Fill Nulls replaces them using a strategy you pick — mean, median, mode, or a custom value. Which is right depends on whether a row without that value still means anything.
Try this in DataLens
DataLens is in private beta. Request access and work through this guide on your own data.
Request beta access