Union All

Stack a second table under this one. Match by name or by position, and tag each row with the file it came from.

What it does

Union All adds the rows of another table to the bottom of yours. Join adds columns. Union All adds rows.

Use it for files with the same shape. Monthly exports, one file per branch, or one file per survey wave all fit.

Append a second table

1. Load the second file first. The picker only lists tables that are already open.
2. Type union in the Search transforms box, in the Pipeline panel.
3. Select Union All in the results.
4. Open the picker under Append rows from. Pick a table.
5. Set Column matching to By name or By position.
6. Click Apply.

With nothing else loaded, the panel shows: No other loaded tables available. Load another file to use Union All.

By name or by position

By name is the default. The panel describes it as Columns matched by name; missing columns become NULL.

Column order does not matter in this mode. A column that only one table has becomes empty in the other table's rows.

By position matches the first column to the first column, and so on. The panel describes it as Columns matched left-to-right; counts and types must align.

Pick By position only when the headers differ but the layout is identical.

Why By position asks you to confirm

Matching by position is a claim about meaning, and nothing in the data backs it up. Two tables with two columns each and no names in common used to append cleanly: one table's cities landed under the other's names, a number column widened to text, and the step reported success.

So the step now shows the pairing it is about to make and waits for you to accept it, every time. If the paired columns hold different kinds of value, it refuses outright and names the first mismatch, for example Matching by position would put "key" (text) into "id" (numeric). Convert one of the two columns first, or match by name.

Tag each row with the table it came from

Fill in Source column and the step adds one more column naming which table each row came from. After stacking twelve monthly files it is the one question the stacked table cannot otherwise answer.

The names are the labels the picker showed, so a view with its own pipeline is named the way you see it in the app. Leave the box empty and no column is added.

What By name does with a missing column

March has an extra channel column:

The result keeps all three columns:

Files and views both work

The picker lists every other view in the workspace. Each entry names the file and the view.

A view with its own pipeline contributes its finished result, not the raw file.

So you can clean February in its own view, then append that clean output here.

Edit that other view later and this step rebuilds with the new rows.

Tips

• Append several files with several Union All steps, one per file.
• Union All keeps duplicates. Add Remove Duplicates after it if you need unique rows.
• Use Source column to keep track of where each row came from. It is easier than adding a column to every file before you append.
• By position fails when the column counts differ, and names both counts. Switch to By name in that case.

For SQL users

With By name selected, the step runs as:

SELECT * FROM "feb_sales" UNION ALL BY NAME SELECT * FROM "mar_sales"

By position drops the BY NAME clause.

A source column is a literal on each side of the union:

SELECT *, 'feb_sales.csv' AS "source" FROM "feb_sales"
UNION ALL BY NAME
SELECT *, 'mar_sales.csv' AS "source" FROM "mar_sales"

Related Operations

• Join - Join with another file/table
• Unnest - Explode delimited column into rows
• Fill Missing - Fill null values