Unnest

One cell holds three answers. Unnest turns that cell into three rows.

What it does

Some columns pack many values into one cell. Survey answers, product tags, and skill lists often look like this.

Unnest cuts the cell at a delimiter. It then writes one row for each piece. The other columns repeat on every new row.

Before

After, split on a comma

Two rows became four. Now you can count each topic, or filter on one topic.

Split a column into rows

1. Type unnest in the Search transforms box, in the Pipeline panel.
2. Select Unnest in the results.
3. Open Select column. Search for the column and click it.
4. Type the separator in the Delimiter box. Use , for a comma.
5. Click Apply.

The step appears in your pipeline with edit and delete buttons.

Things to know

• An empty Delimiter box means a comma. Type the character when your file uses ; or |.
• The delimiter can be more than one character. A value such as - works.
• A row with an empty cell in that column disappears. There is nothing to split.
• The column keeps its position in the grid.
• Spaces stay. The value traffic, parks gives parks with a leading space.

Tips

• Run Text Transform with trim after Unnest. That removes the leading spaces.
• Want columns instead of rows? Use Split Column.
• Group the result with Group & Aggregate to count each value.

For SQL users

Unnesting topics on a comma runs as:

SELECT "response_id", "city", UNNEST(STRING_SPLIT("topics", ',')) AS "topics" FROM "survey"

Related Operations

• Join - Join with another file/table
• Fill Missing - Fill null values
• JSON Extract - Extract data in JSON format into columns