Tutorial 7 of 7

Clean and join data with reusable recipes

Intermediate 7 min

Use Prepare data… to trim, split, deduplicate and join a dataset in an ordered recipe, save the result as a new dataset and re-run the recipe when the source changes.

Exports are rarely ready for a chart: names carry stray spaces, codes mix upper and lower case, a column holds two values and the details you need live in a second table. Prepare data… fixes this with a recipe — an ordered list of operations that runs on a copy of a dataset. The original stays unchanged, and the prepared dataset remembers its recipe so you can apply it again next month.

Open the recipe dialog

Select a dataset under Datasets in the side panel and press Prepare data… in the row of dataset actions. The dialog Prepare data of “…” shows three parts:

New operation — choose an operation and its settings, then press Add operation.
Recipe — the operations in the order they run. Each line is a sentence such as Trim spaces in “Region”. The arrow buttons move an operation up or down, the bin removes it.
Preview — Result: 12 rows, 6 columns and the first 20 rows of the prepared table. It updates after every change.

Nothing is saved until you press Create dataset; Cancel (or Esc) leaves the project as it was.

The six operations

Operation	What it does
Trim spaces	removes spaces at the start and end of every cell of the chosen Column
Lowercase / Uppercase	changes the letters of a column, useful before joining on codes
Remove duplicate rows	keeps the first row of each group of duplicates; with Compare set to Whole row rows must match completely, with a column only that column is compared
Split a column at the first delimiter	cuts the column at the first Delimiter (for example `-` or `,`) and moves the rest into a new column — enter its Name of the new column
Left join another dataset	adds the columns of a second dataset of the project. Pick the Column of this dataset, the Dataset to join and the Matching column in that dataset

The join compares values exactly (North is not north — trim and change case first). Rows without a match are kept with empty cells, and a row that matches several rows of the other dataset is repeated once per match. When a joined column has a name that already exists, Chartroom adds “(2)”.

If an operation cannot run — a column was removed by an earlier step, the delimiter is empty or the result would exceed 50,000 rows, 200 columns or 5 MB of CSV text — a message names the operation and the problem, and the recipe is not changed.

Example: sales plus regional targets

Import the sales export and a second table Regional targets with the columns Region, Target, Manager.
Open the sales dataset and press Prepare data….
Choose Trim spaces, Column Region, press Add operation.
Choose Left join another dataset, Column Region, Dataset to join Regional targets, Matching column in that dataset Region, press Add operation.
Check the preview: every month now carries its target and manager. Press Create dataset.

The new dataset is called “… (prepared)” and is selected. Above its column profiles a box says Prepared from “…” with 2 operations and lists them. Charts and tool steps can use it like any other dataset.

Re-run and edit the recipe

When the source data changes — you used Replace data…, Append rows… or a public-data step refreshed it — open the prepared dataset and press Re-run recipe. Chartroom applies the same operations to the current source and replaces the prepared data; the status bar reports the new number of rows and columns, or The dataset is already up to date. Ctrl+Z undoes a re-run.

Edit recipe… opens the dialog with the stored operations. Update dataset replaces the prepared data and the recipe; Save as new dataset keeps the old version and adds another dataset.

Recipes are saved with the project: autosave, project files, share links and Google Drive keep them.

Reuse a recipe in another project

Download recipe saves the operations as a small JSON file (chartroom-recipe-v1). In another project open Prepare data… on a dataset with the same columns and press Load recipe…. A join refers to the other dataset by its name, so that project needs a dataset with the same name. Files that are not Chartroom recipes, or whose operations do not fit the dataset, are refused with a message and the current recipe stays as it was.

Download CSV in the dialog saves the prepared table without adding a dataset.

Ready to try it?

Launch Chartroom →