Power BI · Lesson 2 of 7

Cleaning data with Power Query

Removing duplicates, fixing types, splitting columns, merging and appending queries.

Power Query (Transform data) cleans data before it reaches your report. Every step is recorded under Applied Steps, so when new data arrives you just press Refresh and the same cleaning happens again.

Common steps:

Set correct data types (dates as Date, amounts as Decimal) — the most important step.
Remove duplicates, blank rows and unwanted columns.
Trim and Clean text; Replace values (“Cochin” → “Kochi”).
Split column by a delimiter; Unpivot months spread across columns into rows.
Merge queries (like a SQL join) and Append queries (stack tables, such as monthly files).

Sample data & steps

Power BI Desktop (free, Windows)

Raw data (Customers.csv):

CustomerID | Name           | City     | Joined
C01        |  anu kumar     | Cochin   | 02-10-2026
C02        | Ravi Menon     | kochi    | 05-10-2026
C02        | Ravi Menon     | kochi    | 05-10-2026
C03        | Meera S        | Trivandrum | 11-10-2026

Power Query steps (Applied Steps):
1. Changed Type          Joined → Date (locale: English (India))
2. Removed Duplicates    on CustomerID
3. Trimmed Text          Name
4. Capitalized Each Word Name
5. Replaced Value        City: "Cochin" → "Kochi"
6. Capitalized Each Word City

Result

CustomerID | Name       | City       | Joined
C01        | Anu Kumar  | Kochi      | 02 Oct 2026
C02        | Ravi Menon | Kochi      | 05 Oct 2026
C03        | Meera S    | Trivandrum | 11 Oct 2026

Download Power BI Desktop free from Microsoft (Windows). Re-create the small sample tables in Excel, load them, and follow the steps. Data is fictional.

Key points

Clean data in Power Query before modelling.
Set data types first — dates and numbers especially.
Every step is recorded and re-runs on refresh.
Merge = join tables; Append = stack tables.

Quick check

You have Jan, Feb and Mar sales files with the same columns. Which operation combines them into one table?

Merge queries Append queries Unpivot Split column

Practice task

Create three small monthly sales CSV files with the same columns, put them in one folder and use Get data → Folder to append them into one table.

Interview corner

What is Power Query used for?

Extracting and transforming data before loading it into the model — cleaning, changing types, removing duplicates, merging and appending tables, unpivoting. Steps are recorded in the M language and repeat on every refresh.

What is the difference between Merge and Append?

Merge combines two tables side by side using a matching key column, like a SQL join. Append stacks rows from tables with the same columns, like SQL UNION ALL.