Free · Beginner · 15 lessons

Data preparation

Free course, nothing to install. Learn to clean, combine and reshape messy data, and to show your work. The course follows one fictional company whose two reports of the same quarter are €5,682 apart, and you find out why.

Start the course
Who it's for
People who get exports and spreadsheets, and have to answer for what they add up to.
If you've worked with rows and columns, you're ready. You don't need any programming experience.
You'll be able to
  • Read numbers and dates the way the file wrote them
  • Find duplicate rows and decide which copy to keep
  • Catch a join that loses and doubles rows while its count stays the same
  • Reconcile two totals to the euro, and explain each step
You leave with
The prepared Q3 table for fictional Vandelay Coffee Supply, with both reports reconciled to the euro, and a bridge template for your own files.

Each lesson starts with the plain answer and its Excel and SQL equivalents, and opens with its data in place, so you can go straight to the topic you need.

New to Strata, Oplema's browser app for data prep? Build your first data flow on a blank canvas first.

Course outline

See the problem

  1. 1What is data preparation?Concept
  2. 2What is data grain? How each step changes a tableConcept

Make the values trustworthy

  1. 3Append vs join: stacking rows from two tablesLab
  2. 4Data types and casting: CAST vs parsingLab
  3. 5Parsing dates from text: day first or month firstLab
  4. 6Null vs blank vs zero: cleaning missing valuesLab

Decide which rows count and add context

  1. 7Sorting data by one or more columnsLab
  2. 8Finding and removing duplicate rowsLab
  3. 9Filtering rows: WHERE, conditions and nullsLab
  4. 10Inner vs left: joining tables by keyLab
  5. 11Calculated columns vs aggregates explainedLab

Reshape and close

  1. 12GROUP BY: grouping and summarizing rowsLab
  2. 13Wide vs long data: pivot and unpivotLab
  3. 14Running totals and rolling averagesLab
  4. 15How to reconcile two reports that disagreeLab

Start with lesson 1.

Reconcile two conflicting reports to the euro, parse values, remove duplicates, audit joins and hand over a table you can defend.