Tutorial 13 of 13
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, RavingDesk 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 datasetRegion, 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. RavingDesk 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 (ravingdesk-data-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 RavingDesk 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.
See also: Import CSV and Excel files the right way.
Ready to try it?