Filter and Data Cleansing

Splitting rows by condition, and bulk-cleaning messy data.

What you'll be able to do

Use basic and custom filters
Use both filter outputs
Clean nulls, whitespace and punctuation in bulk

The Filter tool

Filter splits the stream in two. The T anchor carries rows matching the condition, F carries the rest.

Basic mode gives a field, operator and value. Custom mode takes a full expression:

[Revenue] > 1000 AND [Region] = "East"
[Status] IN ("shipped", "delivered")
!IsNull([Email])

Use the F output

Do not throw away the false branch

Attaching a Browse to the F anchor tells you what you excluded. That is how you discover a filter is removing far more than intended — or that your condition never matches because of a trailing space.

Excluded rows are also often worth writing out as an exception report rather than discarding silently.

Data Cleansing

The Data Cleansing tool does the routine tidying in one place, across as many fields as you select:

Replace nulls with 0 or blank
Remove leading, trailing or duplicate whitespace
Remove punctuation, numbers or letters
Change case to upper, lower or title

Run it early on any text you will later join on. Trailing whitespace and inconsistent case are the two commonest reasons a join silently matches nothing.

Sort and Sample

Sort orders by one or more fields — needed before tools that depend on order, such as Multi-Row Formula.

Sample takes the first N rows, the last N, a random N, or every Nth row. Useful for building a workflow against a small slice before running it against everything.

Key points

Filter splits into True and False outputs — inspect both
Data Cleansing handles nulls, whitespace and case across many fields at once
Clean text keys before joining, or matches fail silently

Check yourself