Lesson 1 of 9 · Lab

Combine CSV files when a column has been renamed

Union stacks tables with the same kind of rows into one. It lines columns up by name, so a column that's been renamed in one file arrives as a second, half-empty column, and nothing warns you.

Union takes any number of inputs and adds a Source column saying where each row came from. By default it keeps every column it finds, filling gaps with empty values.

It's Thursday, October 1, month-end at fictional Kramerica Bikes. Marit wants all three shops in one table, and her question hasn't changed: which shop is short of bikes? As you found in the last lesson of Build a report from a CSV export, Harbour's till now calls amount price. Harbour's file reaches Union through a Select that keeps all twelve of Harbour's columns this time, because month-end needs the ones you dropped in the first course.

Harbour's file has 488 rows, Station Road's 496 and Old Town's 314. How many rows and columns will Union give?

All 1,298 rows. Each file has 12 columns, but Union gives 14: the 11 they share, price, amount and Source.

Stack the shops and fix the rename

  1. In this live flow in Strata, Oplema's browser app for data prep, Count Dataset, the last block, reads 984 rows: only Harbour and Station Road reach Union. Drag from the bottom Manual Input, Old Town's, to Union: 1,298 rows and 14 columns.
  2. Select Union and scroll right: Harbour's rows have a price and an empty amount, and the others the other way around.
  3. Fix the rename in the flow, so every shop's money lands in amount. Check: Count Dataset still reads 1,298 rows, now with 13 columns.
    If you're stuck

    Select the Select block, the one after Harbour's Manual Input. In the price row, select Rename To, type amount, then select the block.

  4. Select Union and set When columns differ to Error on mismatch. Nothing changes, because the names now match.
  5. In Select, clear price's Rename To, then select the block. Union turns red: the columns differ. Type amount back in, and the red clears. Its preview reads columns differ: input 2 does not match input 1, meaning Station Road's columns, on the second wire, aren't Harbour's.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

What the stack should show

1,298 rows and 13 columns. Harbour's rows say input_1, Station Road's input_2 and Old Town's input_3. Before the fix, €18,193.50 of Harbour's amounts sat in price, outside any sum of amount.

One column per meaning

By default a rename fails silently: the flow runs and the total of amount is short by a whole shop. Error on mismatch turns next month's surprise into a red block instead of a smaller number. It checks only names, so two files can still agree on amount and mean different things.

Source holds input_1 to input_3, the order the wires reach Union, not file names.

Your line for Marit: "All three shops are in one table, 1,298 rows. Don't trust it yet."

See the Union, Select and Count Dataset references. For later: Append vs join explains what stacking can't check.

Common stacking mistakes

  • Fixing the file instead of the flow. Renaming the export's header works once; next month's file says price again.
  • Trusting the row count. Rows add up perfectly while a column is half empty. Count the columns too.
  • Joining files that should be stacked. Three shops' bookings are the same kind of row, so they stack. Join attaches different information to a row.

Stacking in SQL and Excel

SELECT *, 'harbour' AS source FROM harbour
UNION ALL BY NAME
SELECT *, 'station' AS source FROM station
UNION ALL BY NAME
SELECT *, 'old_town' AS source FROM old_town;

UNION ALL BY NAME behaves like Union's default. Plain UNION ALL matches by position, and UNION without ALL also drops identical rows. In Excel, =VSTACK(Harbour!A2:L489, Station!A2:L497, OldTown!A2:L315) stacks by position, so a renamed column lands under the wrong heading.