Skip to content
Courses

Power Query

Power Query is the tool in Excel and Power BI for bringing in data and cleaning it with steps that get saved: dropping columns, changing types, filtering rows. When the new file arrives, you click Refresh and every step runs again on its own.

Select any words here to ask your Maestro about them.

You cleaned January's file with these five steps. February's arrives, just as messy. What do you do in Power Query?

A sample query over each month's sales CSV.

What it's for: the work that repeats every week or month: combining files from several stores, dropping columns, fixing types, filtering rows, joining a table to a lookup list. You do it once with clicks, and Power Query replays it every time you refresh. In Excel it's on the Data tab (Get & Transform Data); in Power BI, under Transform data.

How it works: every action becomes a step in the Applied Steps list, in order. Underneath, each step is written in a language called M, which you can read in the Advanced Editor but rarely need to write. Your original file isn't touched: Power Query reads from it and puts the result in a new table.

What to watch: the automatic Changed Type step (leading zeros, dates read as month/day when they're day/month), steps that depend on a column name that changes, and rows a filter drops without telling you. After a refresh, compare how many rows went in and how many came out.

You practice the zeros and the dates in Your CSV looks weird, and cleaning without breaking anything in Clean your data.

How it's used

Applied steps

Query: weekly_sales · Applied Steps

  1. SourceWhere the data comes from: here, weekly_sales.csv in a shared folder.
  2. Promoted HeadersThe first row becomes the column names.
  3. Changed TypeEach column becomes text, a number or a date. Check it: this is where leading zeros vanish.
  4. Removed ColumnsOut go the columns the report doesn't use.
  5. Filtered RowsOut go the rows with no total.
  6. marked: Click Refresh next week: all five steps run on their own over the new file.
Every click you make is saved as a step. That's why next week's cleanup takes one click, and anyone can see what you did to the data.

When to look closer

What does the Changed Type step do?

product_code

  1. 00123
  2. 04410
  3. 10077

If Power Query decides this column is a number, what happens to these codes?

Ask your Maestro about Power Query

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "Power Query" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. What cleanup do you do by hand in Excel every week?

Asking about: Answers from the page "How do I know if my cleaning lost rows? Count before and after every step"

Only this browser keeps what you ask here. How it works

Learn more

Related terms

  • CSV

    A CSV (comma-separated values) is a plain-text file that holds a table: one row per line, with commas between the columns.

  • Grain (of a table)

    What one row of a table stands for: a person, a transaction, a planet, a person per language. Counting rows counts what you want only if you know the grain.

  • Spot check

    Taking a few rows spread across the data (from the start, the middle and the end) and comparing them by hand with what you expect.

  • Reconciliation

    Comparing a total you computed with the same total from an independent source, such as a bank statement or a register report, and explaining any difference.

The whole glossary