Concept
1. Promote headers and remove junk rows
Exports often start with title lines. Home → Remove Rows → Remove Top Rows (e.g. 3), then Use First Row as Headers. Blank rows: Remove Rows → Remove Blank Rows.
2. Remove columns — the safe way
Select the columns you want → right-click → Remove Other Columns.
= Table.SelectColumns(Source, {"OrderID", "OrderDate", "StoreID", "ProductID", "Qty", "UnitPrice", "Discount"})
Why "other"? If the export adds a new column next month, it's ignored automatically. "Remove Columns" (by name) breaks if a removed column is renamed or missing.
3. Trim and Clean text
The capstone StoreID has trailing spaces in about 2% of rows ("S01 " ≠ "S01" — the store lookup would fail). Select StoreID → Transform → Format → Trim (and Clean for hidden characters):
= Table.TransformColumns(#"Removed Other Columns", {{"StoreID", Text.Trim, type text}})
4. Change type — with the right locale
Click the type icon in a column header. For dates in dd-mm-yyyy (Indian exports), use the icon → Using Locale… → Date → English (India):
= Table.TransformColumnTypes(#"Trimmed Text", {{"OrderDate", type date}}, "en-IN")
Set numbers: Qty → Whole Number, UnitPrice → Fixed Decimal (currency) or Decimal, Discount → Decimal. More in Lesson 7.
5. Replace values
Capstone Discount has ~240 blanks (null). Select Discount → Transform → Replace Values: find null → replace 0:
= Table.ReplaceValue(#"Changed Type", null, 0, Replacer.ReplaceValue, {"Discount"})
Text replace works the same (e.g. "Bombay" → "Mumbai"). Untick "Match entire cell contents" only when you mean part of the text.
6. Split column
"Rahul Sharma" → First / Last: Transform → Split Column → By Delimiter → Space → Left-most delimiter. "S01-Delhi" → by "-". Split into Rows (Advanced options) turns "Laptop, Mouse" into two rows — tidy data!
7. Fill down
Reports with merged cells or "group header" layouts:
| Region | Store | Sales |
|---|---|---|
| North | Karol Bagh | 500 |
| (blank) | CP | 450 |
Select Region → Transform → Fill → Down. Blanks must be real null — if they're empty text, first Replace Values "" → null.
8. Add useful columns
- Add Column → Custom Column:
Revenue = [Qty] * [UnitPrice] * (1 - [Discount]) - Add Column → Date → Month → Start of Month (for monthly grouping)
- Conditional Column:
if [Revenue] >= 50000 then "Big" else "Normal"
9. Filter and remove duplicates
Column dropdown filters work like AutoFilter, but are recorded. Home → Remove Rows → Remove Duplicates on selected key columns (e.g. OrderID).
10. A good order of steps
- Remove top/blank rows, promote headers
- Remove Other Columns
- Trim / Clean text
- Change types (with locale)
- Replace / fill down / split
- Filter rows, remove duplicates
- Add calculated columns
- Rename steps (right-click → Rename) so the list reads like documentation
Common mistakes
Changing types before trimming text. Remove Columns instead of Remove Other Columns. Replace "" when the blanks are null (or the reverse). Leaving cryptic step names like "Changed Type3".