Module 5 · Bigger data without bigger effort Lesson 13 of 15

Power Query: clean once, refresh forever

The concept

Every step shows in the Applied Steps list, so a change in the source means editing one step rather than redoing the cleaning by hand.

The mistake is cleaning by hand "just this once"; that once becomes every week, and nobody else can reproduce what you did.

SpreadsheetCopy

Data -> Get Data -> From Text/CSV -> Transform Data

Applied Steps (right pane):
  Source
  Promoted Headers
  Removed Blank Rows
  Changed Type  (date, amount)
  Trimmed Text  (city)
  Removed Duplicates

Close & Load   ->   later, just click Refresh.

Worked example

Import a CSV, remove blank rows, set the date and amount types, and trim one text column; next month point it at the new file and press Refresh.

Your practice task

Run one messy CSV through five Power Query steps and refresh it after editing the source file.

Do this before moving on. Reading a solution teaches the shape of it; typing it teaches you.

Key takeaway

Every step shows in the Applied Steps list, so a change in the source means editing one step rather than redoing the cleaning by hand.

Almost there

15 lessons left of 15

Finish every lesson to unlock the certificate. Each one ends in a practice task — do them and the final project brief writes itself.

Next recommended course Spoken English & Interview Communication