Cleaning & reshaping data

Built-in column transforms — case, trim, fill, split, remove duplicates — plus Summarize, Combine tables, Pivot, and Unpivot verbs that generate query tables.

Two toolsets, for two jobs. Column transforms clean data in place — the fix-this-messy-paste operations, straight from a column's right-click menu. The reshape verbs build a new table shaped differently — grouped, combined, pivoted — by generating a query table for you, no SQL required.

Column transforms

Right-click a column header → Transform…. Select several columns first (Ctrl+click or Shift+click their headers) to transform them all at once — a batch is one operation and one undo, so Ctrl+Z restores everything together.

  • UPPERCASE / lowercase — text cells only; numbers and dates are left alone.
  • Trim spaces — Excel's TRIM: strips leading/trailing whitespace and collapses interior runs to a single space (" a b " → "a b").
  • Fill blanks down — every blank cell takes the nearest non-blank value above it. The classic fixup for data pasted from merged cells or subtotal-style reports.
  • Fill blanks with… — every blank cell takes a value you type. The value is converted to the column's type first, so filling a number column with 0 stores a real zero that SUM can add — and a date column rejects an unparseable value outright rather than half-applying.
  • Split by delimiter… — one column at a time: splits each cell on the delimiter, keeps the first piece in the original column, and inserts new columns (Name 2, Name 3, …) directly to its right for the rest.
  • Remove duplicate rows — deletes rows whose values in the selected column(s) repeat a combination already seen, keeping the first occurrence. Asks before deleting, tells you how many rows went (or that none were found), and is fully undoable.

Transforms always run over the complete data, even on tables far too large to display at once. Calculated columns aren't offered — their values come from their formula, so there's nothing to rewrite; edit the formula instead. And to parse text as dates, convert the column's type from the same right-click menu rather than reaching for a transform — that's the one conversion path.

Reshaping: Summarize, Combine tables, Pivot, Unpivot

The table card's header menu carries four reshaping verbs, right above Query Table…:

  • Summarize… — the pivot-table job: pick columns to group by and values to aggregate (Sum, Average, Count, Count unique, Min, Max), optionally sorted and limited. One output row per group.
  • Combine with another table… — bring columns from a second table in beside the first, matched on a shared column or by row order — like a VLOOKUP for every column at once. Details below.
  • Reshape… → Pivot — values into columns… — a crosstab: the distinct values of one column become the new columns, filled with an aggregate of a value column.
  • Reshape… → Unpivot — columns into rows… — the reverse (Excel refugees may know it as melt or unpivot in Power Query): fold a set of columns into name/value pair rows, ready for grouping and charting.

Create in any of these forms makes an ordinary linked [query table](/docs/query-tables). That's the whole design: nothing new to learn downstream. The result recomputes automatically when its source tables change, shows lineage arrows on the canvas, and charts and formats like any table. The generated SQL sits behind a Show SQL disclosure in the form, and once the table exists it's editable in the query editor — so when you outgrow the form you can take the query over by hand.

The reshape verbs (like all queries) run in the desktop app only — they're not available in the browser editor. Column transforms work everywhere.

Combine tables

Open it from a table card's right-click menu → Combine with another table…, or from Insert → Combine Tables…, where you can also pick the first table.

The form does the obvious setup for you. If another table shares a column name with the first, it picks that table (preferring id-, code-, or key-like names such as Customer ID) and fills in the matching columns. Change either if it guessed wrong.

Match rows by has two choices:

  • A shared column — for example Sales.Region matches Rates.Region. + another column adds a second pair; both must match. Rows to keep is one of Every row of Sales, with matching Rates data added (the default), Only rows found in both tables, or Every row from both tables. If the two columns hold different kinds of values (text versus numbers), the form warns that they won't match.
  • Row order (row 1 with row 1) — pairs rows by position, for two tables that line up but share no column. Rows pair up in their current order, so if either table is sorted later, the pairing changes. If one table is shorter, its missing rows come out blank.

Columns to add from Rates is a checklist, all checked by default. The first table always keeps all its columns. A column whose name already exists in the first table is renamed with the second table's name in front — Rates Amount.

A live preview shows how many rows of the first table found a match (1,180 of 1,200 Sales rows have a match in Rates), and warns when some keys match more than one row, so the result would grow (Some keys match more than one Rates row — the result will have 1,240 rows.). On very large tables the preview may be based on just the first rows.

The result keeps the first table's row order.

← Query tablesScripts →