Transform & data quality | ATX AnalyticsTable Handbook

Transform & data qualitySort, filter, recode, dedupe — then find the problems you cannot see. The Transform tab is everything that changes the data rather than how it looks. Two things distinguish it from the equivalents in a desktop grid tool. First, operations on pinned data are durable: sorting a pinned region records an ORDER BY on the pin, so a refresh returns the sorted order rather than losing it. Second, the data-quality tools address the failures that actually break reports — invisible characters, markup left over from a paste, and statistical outliers — rather than only the ones that are easy to spot.

What it does

Sort on up to three keys, numeric-aware, whole sheet or selection, with horizontal sorting supported
Point-and-click column filters, saved with the project and re-run against current data on reopen
Text to Columns, Merge Columns, Value Map with clustering suggestions and reusable mapping tables, Dedupe by chosen keys
Value lists — named sets of allowed values sourced from typed lines, a sheet range or a query's DISTINCT values, enforced as validated dropdowns
Anomaly scan: non-breaking spaces, zero-width characters, control codes, smart quotes, stray tabs, trailing whitespace, plus HTML and Office export markup decoded or removed
Outlier detection: z-score, modified z-score (MAD), IQR fences, isolation forest, DBSCAN, rare-category, category drift, impossible dates, time gaps and day-of-week seasonality

How it works

Filtering a very large static pin runs as a background job with progress and a Stop button, so the window never freezes.
Filters are re-applied against the current data on reopen rather than replaying stale row numbers, so rows added or edited meanwhile are filtered correctly.
Anomaly fixes are applied selectively — the scan proposes, the analyst disposes.
Outlier results are ranked by score and can be written onto a sheet as a report for review or sign-off.
Value lists store the list's canonical spelling, so grouping stays clean no matter how the value was typed.

Practices that keep it reliable

Run the anomaly scan on anything that arrived by paste or by e-mail before trusting a single aggregate over it
Treat outlier detection as a question, not a verdict — the report exists so a human decides
Whenever a cleanup will be needed again tomorrow, build it as a pipeline step instead of a ribbon action

In a regulated environment

Filters and lists are stored as JSON in the project folder and tracked in Git, so a change to what a report excludes is visible in review.
Data-quality findings can be written onto a sheet and exported, which turns them into evidence rather than a screenful of colour.

Screens

Transform — data work: sorting, filtering, recoding, quality checks, pipelines and privacy.
Sort — up to three keys; on a pinned region it is recorded as an ORDER BY and survives refresh.
Value Map — recode many spellings to one canonical value, with reusable mapping tables.
Dedupe — by chosen key columns, or compact in place.
Text to Columns and Merge Columns for the everyday reshaping work.
Value lists — allowed values from typed lines, a range, or a query's DISTINCT column.
Anomalies — the text problems that are invisible on screen and fatal in a join.
Outliers — nine detectors across numeric, categorical and datetime columns, ranked by score.

Related guides

Data preparation: definition

ATX Institute on LinkedIn

This site needs JavaScript for the full experience. Every page above is reachable without it.