Stop 02 · Excel & SpreadsheetsTopics · 7/10

Power Query

In short

If you clean the same messy file every Monday, Power Query will change your life. Record the cleaning once, then press Refresh every week after.

Excel's built-in tool for importing, cleaning and reshaping data through a repeatable, recorded set of steps — so you don't have to manually re-clean a dataset every time it refreshes.

The jaadu feeds dough into a small roti machine that stacks round rotis.

Think of it like this

The roti-making machine

Making rotis by hand means rolling each one every day. A roti machine is set up once: dough in, perfect rotis out, every time. Power Query is that machine for data. You set the steps once, and fresh data comes out clean on every refresh.

Watch it happen

Applied steps on a weekly sales file

Get data

Remove rows

Fix types

Split col

Merge

Load

1/6Data > Get Data > From File. Point it at the CSV or a whole folder of weekly files.

The big ideas

What you need to know

Applied Steps are the recipe

Every click is recorded on the right. You can rename, reorder or delete steps later.

Combine a whole folder

Point at a folder and Power Query stacks every file inside into one table.

Raw file stays untouched

It reads the source and outputs a clean copy. Your original data is never edited.

There's code underneath

Each step is written in a language called M. You don't need it to start, but it's there.

Save yourself the pain

Common mistakes

Oops

Cleaning in Power Query, then editing the output table by hand.

Do this instead

Do every fix as a step. Manual edits vanish on the next refresh.

Oops

Renaming columns in the source file after building the query.

Do this instead

Queries look for columns by name. Keep source headers stable or update the step.

Your mission

Automate a weekly clean

~35 min

Quick check

Why use Power Query instead of cleaning by hand each week?

Go deeper

1Import a CSV through Power Query and apply 2-3 transformation steps.
2Learn to refresh a query and see your cleaning steps automatically reapply.
3Understand why Power Query beats manual cleaning for any recurring report.