Splitting Multi-Value Cells into Separate Rows

Your product data has a "tags" column. Each cell contains something like "electronics, sale, featured". Three values crammed into one cell, separated by commas.

This is fine for display. It's terrible for analysis. You can't count how many products are tagged "sale" because "sale" isn't a distinct value - it's buried inside a string. You can't filter by a single tag, group by tag, or join on tag. The data is locked up.

What you need is one row per tag, where the product appears three times (once for each tag) with all its other columns duplicated. This operation is called unnesting, and it's one of the most useful transformations for anyone working with real-world data.

What multi-value cells look like

Here's the starting point:

Three rows, but the tags column is doing too much. You can't answer "how many products are on sale?" without parsing strings.

Unnest: one row per value

In ExploreMyData, click the green + in the Pipeline panel and select Unnest from the Data group. The panel has two fields. Choose the column holding the delimited values (here, "tags") and set the delimiter to a comma (,).

Click Apply. The three rows become nine:

Each tag now has its own row. Product 101 appears three times, 102 twice, 103 four times, and the product_id and name values repeat down each block. That repetition is the point: every tag now sits next to the product it belongs to, as a value you can filter and group on.

The SQL behind it

Open the pipeline to see what's happening:

SELECT "product_id", "name",
       UNNEST(STRING_SPLIT("tags", ',')) AS "tags"
FROM products

STRING_SPLIT("tags", ',') turns "electronics, sale, featured" into a list: ['electronics', ' sale', ' featured']. UNNEST() takes that list and expands it into separate rows. DuckDB handles the duplication of the other columns automatically. The AS "tags" matters too: the unnested column replaces the original in place rather than arriving as a new one, so nothing is left holding the old comma-separated string. products here is just the file tab name; yours will read whatever your file is called.

Trim the whitespace

Look at the result table again. The split values have leading spaces: " sale" instead of "sale". That's because the original data was "electronics, sale, featured" with spaces after the commas. The split kept the spaces.

Fix this by adding a Text Transform step right after the unnest. Select the tags column and choose "trim". This strips leading and trailing whitespace from every value.

Now your tags are clean: "electronics", "sale", "featured" - no stray spaces.

What you can do after unnesting

With tags in separate rows, analysis becomes straightforward:

Count tag frequency: Group & Aggregate with Group by set to tags and one aggregation, COUNT of product_id. The result column is named product_id_count. Across these three products, "sale" shows up twice and "featured" twice; the other five tags appear once each, which accounts for all nine rows.

Filter by tag: Add a Filter step on tags with the "equals" operator and the value sale to see only products on sale. Exact matching works now that each cell holds one tag.

Pivot by tag: With a category column in the data, Pivot gives you a cross-tabulation: Rows = category, Columns = tags, Values = COUNT of product_id. One column per tag, one row per category.

None of this was possible when the tags were stuck together in one cell.

Other delimiters

Commas are the most common delimiter, but the same technique works for any separator:

• Semicolons: "skill1; skill2; skill3" - set delimiter to ;
• Pipes: "red|blue|green" - set delimiter to |
• Slashes: "US/CA/MX" - set delimiter to /
• Spaces: "tag1 tag2 tag3" - set delimiter to a space

Understanding the row count increase

Unnesting increases your row count. If you started with 1,000 products and each has an average of 3 tags, you'll end up with roughly 3,000 rows. This is expected and correct - you're not duplicating data, you're normalizing it.

Keep the original row count in mind when doing aggregations after unnesting. If you sum revenue after unnesting tags, a product with 3 tags will contribute its revenue 3 times. Group by product_id first if you need accurate totals, or do your revenue calculations before the unnest step.

When not to unnest

Unnest is the right tool when you need to analyze or filter by individual values within a multi-value cell. It's overkill if you only need to know whether a cell mentions a specific value. For that, a Filter step on the original tags column with the "contains" operator and the value sale is simpler, and it leaves your row count alone.

Use unnest when you need to count, group, or join on individual values. Use string matching when you just need to filter.

Try unnesting your data →