Converting Yes/No/True/False/1/0 to a Consistent Boolean

You're looking at an is_active column. It should be simple - either the account is active or it isn't. But the values tell a different story: "Yes", "Y", "TRUE", "true", "1", "yes", "True". And that's just the truthy side. The false values are equally chaotic: "No", "N", "FALSE", "0", "false", "no".

This happens when data comes from multiple sources, or when different people entered it manually, or when someone exported from a system that uses "Y/N" and another that uses "true/false". The result: you can't filter for active accounts reliably. A filter for "TRUE" misses the rows that say "Yes". A filter for "1" misses the rows that say "true".

Here's how to collapse all of that into a clean boolean column in ExploreMyData.

What the data looks like

Here's a sample from a user accounts export:

Two columns, both supposed to be boolean, both a mess. The is_active column alone has seven distinct values that represent just two states.

Step 1: Map all values with Bulk Replace

The fastest approach is Bulk Replace. This operation lets you define groups of values and what each group should become. Click the green + in the Pipeline panel and select Bulk Replace from the Transform group, then choose the is_active column. The panel lists every distinct value with its count, so you can tick the truthy ones, name the group, and hit + Create.

Define two groups:

• Group 1: "Yes", "Y", "TRUE", "true", "True", "1", "yes" → replace with true
• Group 2: "No", "N", "FALSE", "false", "False", "0", "no" → replace with false

The SQL that ExploreMyData generates is a CASE WHEN with every variation mapped:

CASE WHEN CAST(is_active AS VARCHAR) IN ('Yes', 'Y', 'TRUE', 'true', 'True', '1', 'yes') THEN 'true' WHEN CAST(is_active AS VARCHAR) IN ('No', 'N', 'FALSE', 'false', 'False', '0', 'no') THEN 'false' ELSE CAST(is_active AS VARCHAR) END

The CAST(is_active AS VARCHAR) wrapper isn't decoration. Group names are text, so the output of the CASE is text, and every branch has to agree on a type. Casting the column to VARCHAR on both the comparison and the fallback is what lets Bulk Replace work on a numeric or date column too, mapping 1/2/3 to 'low'/'mid'/'high' without DuckDB trying to coerce 'low' into an integer and erroring out.

That ELSE ... END at the end is a safety net. If there's a value you didn't account for, it stays as-is rather than silently becoming NULL. You'll spot it when you check the results.

Step 2: Store it as an actual BOOLEAN (optional)

After the Bulk Replace, every row in is_active says either "true" or "false". But it's still a text column. The values are strings, not booleans.

For most work you can stop here. ExploreMyData treats BOOLEAN columns as categorical, exactly like the two-value text column you just produced, so the explorer card, equality filters and search all behave the same either way.

If you want the stored type to really be BOOLEAN, Convert Type won't get you there - its dropdown offers text, numeric and date. Use SQL Query from the Advanced group instead:

SELECT * REPLACE (TRY_CAST(is_active AS BOOLEAN) AS is_active) FROM pipeline_output

REPLACE swaps the expression in and leaves the column where it is; an EXCLUDE plus a trailing expression would work too, but it moves is_active to the far right of the grid. On the table name: pipeline_output is the cumulative result of every step so far, which is what you want here. If this is the very first step in the pipeline, use the file's own table name from the file tab instead.

TRY_CAST is important here. If there's a stray value that didn't get caught by the Bulk Replace, say someone typed "active" instead of "true", TRY_CAST returns NULL for that row instead of throwing an error. A regular CAST would fail the entire operation.

Here's the shortcut nobody mentions: DuckDB's VARCHAR to BOOLEAN cast is more forgiving than you'd expect. It accepts true/false, t/f, yes/no, y/n, on/off and 1/0, and it doesn't care about case. That covers every value in the sample table above. So if your mess happens to be made only of those spellings, you can skip the Bulk Replace entirely and run the SQL step on its own. Do check the result for NULLs afterwards, because that's where anything the cast didn't recognise ends up. The Bulk Replace route is still worth it when you have house variants like "Active", "Deactivated" or "1 - yes", which no cast is going to guess.

Now it works like a boolean

With a real BOOLEAN column, everything just works:

• Filtering - filter where is_active = true catches every active user, not just the ones who happened to have "TRUE" in uppercase.
• Counting - group by is_active and you get two rows: true and false. Not seven.
• Logic - you can use it in conditions with other boolean columns, like finding users where is_active = true AND email_verified = false.

Handling multiple boolean columns

In the sample data, email_verified has the same problem. Repeat the Bulk Replace for that column. If you have five or six boolean columns in your dataset - which is common in user exports - you'll do this for each one.

Each pass adds one pipeline step, or two if you also want the BOOLEAN cast. It takes about a minute. The alternative - writing the SQL by hand, testing it, handling edge cases - takes significantly longer.

Watch for edge cases

A few things to check after converting:

• NULLs. If some rows had no value to begin with, they'll remain NULL after conversion. That's correct - a missing value isn't true or false, it's unknown.
• Unexpected values. Check Column Explorer after the Bulk Replace. If you see any value besides "true" and "false", you missed a variant. Add it to the appropriate group.
• Numeric booleans. "1" and "0" are common but so are "2" or "-1" in some systems. Decide if "2" means true or if it's an error.

Fix your boolean columns →