Lesson 2 of 11 · 10 min · Getting data in

Power Query: cleaning and shaping data

Covers: Power Query in Power BI Desktop. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • What Power Query is and how Applied Steps record your work
  • The everyday transformations: types, filters, split, replace, trim, duplicates
  • Combine tables with Merge and Append
  • Unpivot columns into rows and group rows
  • Add custom columns and understand query folding

In simple words

Power Query is a clean-up workshop between the raw data and your report. You open a table, click things like "remove blank rows", "split this column at the comma" or "change this to a date", and Power Query writes each click down as a step. The next time the data refreshes, it replays every step on the new data, so you never have to clean it by hand again.

Words to know

  • Power Query: the data preparation tool inside Power BI and Excel.
  • Applied steps: the recorded list of transformations that run in order on every refresh.
  • M: the formula language behind Power Query steps.
  • Query folding: Power Query sending your steps to the database to do the work there.

The editor

Open it with Home > Transform Data. The window has the list of Queries on the left, a data preview in the middle, the ribbon on top and Applied Steps with the formula bar on the right. Click a step to see the data as it was then; delete or reorder steps if needed. Each query is one table. Give queries clear names (Sales, Customers, not Query1), because those names appear in the model.

Power Query is non-destructive: it never changes the source. Steps are written in M, and Home > Advanced Editor shows the whole script, which is useful for copying and for learning.

Always start with types and headers

  1. Use the first row as headers (Home), if needed.
  2. Set the data type of every column: the icon at the left of each header (ABC, 123, calendar). Wrong types cause errors in visuals and DAX. Right-click a column > Change Type. Use Using Locale when dates or numbers use another format (for example dates as day/month/year: choose Date and the English (India) locale).
  3. Remove errors (Home > Remove Rows > Remove Errors) only after you understand why they appear; first use Keep Errors to look at them.

Common transformations

Goal How
Remove unwanted columns select columns > Remove Columns (better: Choose Columns, so new unwanted columns are ignored)
Filter rows the drop-down on a header; filter out null, blanks and test data
Remove duplicates select the key columns > Remove Duplicates
Fix text Transform > Trim, Clean, Capitalize Each Word, Replace Values
Split a column Split Column by delimiter (for example "Last, First") or by number of characters
Fill gaps Fill Down for category labels that appear only on the first row of a group
Extract date parts Add Column > Date > Year, Month Name, Day of Week
Replace nulls Transform > Replace Values > null with 0 or "Unknown"
Rename and reorder double-click header; drag columns

Order matters: for example, trim text before removing duplicates and before merging, or "Pune " and "Pune" will not match.

Combine tables: Append and Merge

  • Append Queries stacks tables with the same columns (the Excel concat or SQL UNION ALL): monthly sales files into one table. With a Folder source, Combine Files does it automatically for every file in a folder, including future ones.
  • Merge Queries matches rows on key columns (the SQL join). Choose the join kind: Left Outer (all from the first table), Inner, Full Outer, Left Anti (rows with no match, used to find missing records). After merging, expand the new column to pick the fields you want.
Orders  Merge with  Customers  on CustomerID  (Left Outer)  → expand Name, City

Check row counts after a merge: if the second table has duplicate keys, rows multiply (the join fan-out you met in the SQL course).

In a star-schema model you often do not merge dimension fields into the fact table; you keep separate dimension tables and relate them (the modelling lessons). Use merge for lookups that really belong in one table or for cleaning.

Unpivot and group

Spreadsheets often have a column per month. Reporting tools prefer one row per measurement. Select the identifier columns > Transform > Unpivot Other Columns:

Product | Jan | Feb | Mar        →        Product | Month | Sales
Pen     | 100 | 120 | 90                 Pen     | Jan   | 100
                                          Pen     | Feb   | 120
                                          Pen     | Mar   | 90

(Use "Unpivot Other Columns", not "Unpivot Columns", so that new months added later are included.)

Group By (Transform) summarises rows, like SQL GROUP BY. Use it sparingly in Power Query; it is usually better to keep detailed rows in the model and let DAX measures do the summarising, which keeps every visual flexible.

Custom columns and M

Add Column > Custom Column lets you write M formulas. Examples:

= [Quantity] * [UnitPrice]
= if [Amount] >= 1000 then "High" else "Normal"
= Text.Trim([City])
= Date.StartOfMonth([OrderDate])

M is case sensitive, and uses if ... then ... else. Also useful: Column From Examples (type the result you want for a few rows and Power Query guesses the formula) and Conditional Column (a form-based if/else).

Parameters (Home > Manage Parameters) let you store things like a folder path or a start date once and reuse them.

Query folding

When the source is a database, Power Query tries to translate your steps into one SQL query and send it to the database, so filtering and grouping happen there. This is called query folding, and it is the difference between pulling 10 million rows and pulling only the 50,000 you need. Right-click a step and look at View Native Query: if it is enabled, that step is folded. Steps that cannot be translated (some custom M functions, merging with a non-folding source) break folding, and everything after them runs on your machine. Practical rules:

  • Put filters and column removal first, as early as possible.
  • Do simple, foldable steps (filter, choose columns, rename, merge from the same database) before complex ones.
  • If a step breaks folding on a large table, consider moving the logic into a database view.

Query folding does not apply to Excel and CSV sources (there is no engine to push the work to), so keep those files small.

Tidy habits

  • Name every step in a meaningful way (right-click > Rename).
  • Disable Enable load for staging queries that only feed other queries.
  • Avoid hard-coded values and file paths; use parameters.
  • Keep the first queries tidy and generic, and put transformations in a layered order: raw, cleaned, final.
  • Document unusual steps with a comment in the Advanced Editor (// remove test accounts).

Common mistakes

  • Forgetting data types, or leaving the auto-detected "Changed Type" step unreviewed.
  • Using Unpivot Columns rather than Unpivot Other Columns.
  • Cleaning after merging, so keys do not match.
  • Doing heavy steps before filtering, and breaking folding.
  • Pulling in every row and column and filtering later.

Try it yourself

  1. Load a messy CSV in Power Query, set types, trim text and remove duplicates; look at the Applied Steps.
  2. Append two monthly files, or combine a folder of files.
  3. Merge orders with customers (Left Outer), expand city, then check the row count against the orders table.
  4. Unpivot a table with a column per month into Month and Sales columns.
  5. Add a conditional column for "High" and "Normal" orders and a custom column for line total.

Check yourself

What are Applied Steps?

A recorded, ordered list of the transformations you made, which Power Query replays on every refresh.

When do you use Append and when Merge?

Append stacks tables with the same columns (like UNION ALL); Merge joins tables on matching key columns (like a SQL join).

Why choose "Unpivot Other Columns"?

It keeps working when new columns (such as new months) are added to the source, because it unpivots everything except the identifier columns you selected.

What is query folding?

Power Query translating your steps into a source query (SQL) so that the database does the filtering and grouping.

Interview questions

What is Power Query and where does it sit in the Power BI workflow?

It is the data preparation layer: it connects to sources, cleans and reshapes data with recorded steps, and loads the result into the model, where relationships and DAX measures build on it.

How would you combine 12 monthly Excel files?

Use the Folder connector and Combine Files so Power Query appends them with one transformation; new files in the folder are included on the next refresh.

Why do steps order and filtering early matter for performance?

Early filters and column removal reduce the amount of data processed and preserve query folding, so the database does the work instead of the local engine.

Summary

  • Power Query records non-destructive Applied Steps that replay on every refresh.
  • Fix types first; then clean text, filter, split, fill and remove duplicates.
  • Append stacks tables, Merge joins them, Unpivot Other Columns reshapes wide data.
  • Keep steps foldable, filter early and name queries and steps clearly.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on Power BI and dashboards for Data Analyst and scores every answer, with what to fix.