Ravindra BagaleCourses & study guides Track your progress

Chapter 51

51.1 Data Types and Dirty Data Cleaning

By Ravindra Bagale · Power BI deep series

Chala mitrano — data can be very dirty. We learn every Power BI data type, then clean blanks, bad dates, mixed units, PDF junk and more — step by step.

Fictional sample data for learning. All company names and brands belong to their owners. We use the names only so students can understand. Numbers on this page are made up.
After this lesson you can
  • Name every common Power Query data type and when to use it.
  • Fix locale pitfalls for dates, decimals and currency symbols.
  • Clean blanks, placeholders, spaces, case, spelling and duplicates.
  • Repair numbers-as-text, mixed dates, Excel serials and bracket negatives.
  • Reshape merged headers, totals, crosstabs (unpivot), splits and merges.
  • Normalise units and currencies; protect leading zeros and UTF-8 text.
  • Handle orphans, folder combine, JSON, PDF junk, time zones and fiscal years.
  • Use column profiling and a quality checklist before Close & Apply.

Start with a problem (fictional). Priya opened a fictional Pune Kirana Weekly Excel from store managers. It had a title row, Bombay and mumbai mixed, ₹ and $ in one column, Excel serial dates, a Total row, a repeated header in the middle, PIN codes that lost zeros, and one OrderId typed ORD-100l. The CEO dashboard was wrong by lakhs. She needs types + a cleanup playbook.

Sample layout — fictional data: dirty file through Power Query cleanup steps to clean model
Sample layout — fictional data (not a real Service screenshot)

1. Why data types decide if your report is true

Why: A column that looks like numbers but is Text will not sum. A date that is Text will break YTD. Wrong types silently lie.

What: In Power BI, every column has a data type (Text, Whole Number, Decimal, Date, True/False, and more). Power Query sets types when you import. The model uses those types for filters, relationships, and DAX.

How — check types first:

Vertical steps (one under another)

  1. Open Power Query (Transform data).
  2. Look at the icon under each column header (ABC, 123, calendar, T/F).
  3. Click a column — Home / Transform — Data Type — set the correct type.
  4. If the change fails, fix dirty values first, then set the type.
  5. Close & Apply, then check a simple SUM or date slicer.

Tiny example: Pune kirana sheet: Amount shows as ABC (Text). Card shows blank or error. Change to Decimal Number, then card shows ₹1,24,500 (fictional).

Ravindra Bagale's Tip

Friends, never trust auto-detect alone. Always open Power Query and read the column icons.

2. Every Power BI / Power Query data type

Why: Interviews and real files expect you to name types and pick the right one.

What: These are the main types you set in Power Query. Icons sit under the column name.

How — match type to meaning:

Vertical steps (one under another)

  1. Text for labels and codes that must not become numbers (PIN, phone, order id with leading zeros).
  2. Whole Number for counts; Decimal for money and measures that need fractions.
  3. Fixed Decimal when you need currency-style fixed precision.
  4. Percentage for rates stored as fractions (0.18).
  5. Date / Time / DateTime / DateTimeZone / Duration for time fields — pick the narrowest that fits.
  6. True/False for yes/no flags. Avoid Binary in star-schema facts.

Tiny example: MH PIN 411001 must stay Text. If it becomes Whole Number, leading zeros vanish, then 411001 still ok for Pune, but 001234 becomes 1234 (broken id).

Ravindra Bagale's Tip

Friends, PIN codes, GSTIN, and employee IDs are Text — even when they look numeric.

Power Query data types (learning table — fictional examples)
TypeIcon ideaUse forTiny example
TextABCNames, cities, IDs that must keep leading zeros (PIN 411001)City = "Pune"
Whole Number123Counts, qty without fractionsQty = 12
Decimal Number1.2Most measures: sales, weight, rateGMV = 124500.50
Fixed Decimal (Currency)₹Money with 4 decimal precision fixedPrice = 99.9900
Percentage%Rates stored as 0.18, then shows 18%GST = 0.18
Date📅Calendar day only2026-03-04
Time⏱Time of day14:30:00
Date/Time📅⏱Timestamp2026-03-04 14:30
Date/Time/Zone🌐Timestamp + offset2026-03-04 14:30+05:30
Duration⏳Length of time1.02:30:00
True/FalseT/FFlagsIsActive = TRUE
Binary⚙Files, images (rare in models)blob

About this data: Examples use fictional Pune / Nashik retail numbers for learning only.

3. Auto-detection pitfalls and locale (dates, commas, currency)

Why: 03/04 can mean 3 April (India/UK) or March 4 (US). 1.234,56 vs 1,234.56. ₹ $ £ € symbols confuse type detect.

What: Power Query uses a locale when it guesses types. Wrong locale, then wrong dates, wrong decimals, failed type changes.

How — set locale and parse safely:

Vertical steps (one under another)

  1. File — Options — Current file — Regional settings — set locale (e.g. English India) to match the file.
  2. Or: change type Using Locale… (right-click column) — pick type + locale.
  3. For mixed currency symbols: strip symbols with Replace Values / Text.Select, then Decimal.
  4. Never assume 03/04 — ask the source owner which day is month.
  5. Prefer ISO dates in clean models: yyyy-mm-dd.

Tiny example: CSV from London office: 04/03/2026 means 4 March. US locale reads April 3 — YoY chart shifts by a month (fictional Contoso UK vs US).

Ravindra Bagale's Tip

Friends, Using Locale is interview gold. Say: I change type with locale, I do not guess 03/04.

Locale: Indian date vs US date

About this data: Fictional Blinkit-style order extract for Maharashtra training.

Why it happens: File made in India (dd/mm/yyyy). Auto US locale swaps day/month.

Before (dirty)

OrderDate_raw
03/04/2026

After (clean)

OrderDate
2026-04-03

Power Query steps (vertical):

  1. Select OrderDate_raw
  2. Data Type — Using Locale — Date — English (India)
  3. Rename to OrderDate
  4. Verify sample rows against the source email
// M idea
= Table.TransformColumns(Source, {{"OrderDate_raw", each Date.From(_, "en-IN"), type date}})

4. Nulls, blanks and placeholder junk

Why: Empty cells and NA/- break averages and relationships.

What: Treat blank, null, NA, dash, and fake zero as different stories — ask the business before you replace.

How — clean missing values:

Vertical steps (one under another)

  1. Profile the column (section 11)
  2. Replace placeholders with null or a label
  3. Decide null vs 0 with the owner
  4. Document the rule in the query name

Tiny example: HR Status NA, then (missing); GMV blank, then ask before zero (fictional Acme Pune).

Ravindra Bagale's Tip

Friends, zero and missing are not the same. Replacing blindly breaks KPIs.

Blanks and nulls

About this data: Fictional city GMV for Pune kirana chain.

Why it happens: Export left empty cells; systems store missing as null.

Before (dirty)

CityGMV
Pune45000
12000
Nashik

After (clean)

CityGMV
Pune45000
(unknown)12000
Nashik0

Power Query steps (vertical):

  1. Replace Values on City: null/blank, then "(unknown)" (or remove rows if policy says so)
  2. Replace null GMV with 0 only if business agrees missing means zero
  3. Or keep null and use DAX that handles BLANK()
= Table.ReplaceValue(Source, null, "(unknown)", Replacer.ReplaceValue, {"City"})
Placeholders: NA, -, 0, N/A, #N/A

About this data: Fictional HR status sheet from Acme Pune.

Why it happens: Excel users type NA or dash when they do not know the value. Zero may mean missing, not real zero.

Before (dirty)

Status
NA
-
0
Active

After (clean)

Status
(missing)
(missing)
(missing)
Active

Power Query steps (vertical):

  1. Replace Values: "NA", "N/A", "-", "#N/A", then null or "(missing)"
  2. Ask business: is 0 a real zero or a placeholder?
  3. Do not average placeholders as numbers
= Table.ReplaceValue(Source, "NA", null, Replacer.ReplaceValue, {"Status"})

5. Spaces, case, spelling and duplicates

Why: Looks-the-same text fails relationships and distinct counts.

What: Trim/Clean first. Then standardise spelling with a map. Then remove true duplicate keys.

How — normalise text:

Vertical steps (one under another)

  1. Trim + Clean
  2. Standardise case
  3. Replace known aliases (Bombay, then Mumbai)
  4. Distinct on real keys

Tiny example: Bombay / mumbai / Mumbai, then Mumbai; OrderId A1 duplicated (fictional).

Ravindra Bagale's Tip

Friends, Trim + Clean before any fuzzy match. Garbage in, fuzzy mess out.

Extra spaces and non-printing characters

About this data: Fictional customer list — Indian and German names.

Why it happens: Copy-paste from web/PDF leaves NBSP and double spaces.

Before (dirty)

Customer
Ravi Patil
Sofia Müller

After (clean)

Customer
Ravi Patil
Sofia Müller

Power Query steps (vertical):

  1. Transform — Format — Trim
  2. Transform — Format — Clean (removes non-printable)
  3. Replace " " with " " in a loop if needed
= Table.TransformColumns(Source, {{"Customer", Text.Clean}, {"Customer", Text.Trim}})
Inconsistent case and spelling (Mumbai/Bombay/mumbai)

About this data: Fictional logistics cities — India.

Why it happens: Old name Bombay still typed; case differs by who typed.

Before (dirty)

City
Mumbai
Bombay
mumbai
MUMBAI

After (clean)

City
Mumbai
Mumbai
Mumbai
Mumbai

Power Query steps (vertical):

  1. Format — Capitalize Each Word (or UPPER then map)
  2. Replace Values: Bombay, then Mumbai
  3. Keep a mapping table CityAlias, then CityCanonical for many aliases
= Table.ReplaceValue(Source, "Bombay", "Mumbai", Replacer.ReplaceText, {"City"})
Duplicates — exact and near

About this data: Fictional orders — Contoso training.

Why it happens: Double export or same person typed two ways.

Before (dirty)

OrderIdCustomer
A1Ravi
A1Ravi
A2Ravi Patil

After (clean)

OrderIdCustomer
A1Ravi
A2Ravi Patil

Power Query steps (vertical):

  1. Home — Remove Rows — Remove Duplicates on OrderId (exact)
  2. For near-duplicates: Trim/Clean/lowercase first, then fuzzy merge or mapping table
  3. Never remove duplicates blindly on name alone — two customers can share a name
= Table.Distinct(Source, {"OrderId"})

6. Numbers-as-text, date chaos, serials and brackets

Why: Wrong types and formats break SUM, YTD and joins.

What: Strip symbols, parse with locale, convert Excel serials, turn (1,200) into -1200.

How — fix types:

Vertical steps (one under another)

  1. Strip currency and thousands separators
  2. Using Locale for dates
  3. Serial, then Date
  4. Brackets, then negative

Tiny example: ₹12,450 text, then 12450 decimal; Excel 45321, then date (fictional).

Ravindra Bagale's Tip

Friends, if Change Type shows errors, fix values first — do not only Replace Errors and forget.

Numbers stored as text

About this data: Fictional Mumbai fashion boutique sales.

Why it happens: CSV exports quote numbers; users type currency symbols.

Before (dirty)

Amount
"12,450"
₹ 9,900
1200

After (clean)

Amount
12450
9900
1200

Power Query steps (vertical):

  1. Replace "," and "₹" and spaces with empty
  2. Change type to Decimal Number
  3. If errors: use Replace Errors or split bad rows to an Errors query
= Table.TransformColumns(Source, {{"Amount", each Number.From(Text.Replace(Text.Replace(_, "₹", ""), ",", "")), type number}})
Mixed date formats in one column

About this data: Fictional multi-branch retail dates.

Why it happens: Three teams pasted into one Excel column with different habits.

Before (dirty)

OrderDate
03-Apr-2026
2026/04/05
04/06/2026

After (clean)

OrderDate
2026-04-03
2026-04-05
2026-06-04

Power Query steps (vertical):

  1. Try Change Type Using Locale for the majority format
  2. For stubborn rows: add a custom column with Date.FromText and culture
  3. Or split sources and append after each is parsed
= Table.AddColumn(Source, "OrderDate2", each try Date.FromText([OrderDate], "en-GB") otherwise Date.FromText([OrderDate], "en-US"))
Excel serial dates (45000 looks like a number)

About this data: Fictional Excel dump from warehouse.

Why it happens: Excel stores dates as day counts from 1899-12-30. Export as number, then serial appears.

Before (dirty)

OrderDate
45321
45322

After (clean)

OrderDate
2024-01-30
2024-01-31

Power Query steps (vertical):

  1. Change type to Date (Power Query understands Excel serials when typed as number then Date)
  2. If text serial: Number.From then Date.From
  3. Verify against a known holiday row
= Table.TransformColumns(Source, {{"OrderDate", each Date.From(Number.From(_)), type date}})
Negative numbers in brackets accounting style

About this data: Fictional finance sheet — JP Morgan style training numbers (made up).

Why it happens: Finance Excel shows credits in parentheses.

Before (dirty)

Amount
(1,200)
3,400

After (clean)

Amount
-1200
3400

Power Query steps (vertical):

  1. Replace "(" with "-" and ")" with ""
  2. Remove thousands commas
  3. Decimal type
= Table.TransformColumns(Source, {{"Amount", each Number.From(Text.Replace(Text.Replace(Text.Replace(_, "(", "-"), ")", ""), ",", "")), type number}})

7. Shape problems: headers, totals, unpivot, split, merge

Why: Pretty Excel reports are not data tables.

What: Skip title rows, kill mid-file headers and totals, unpivot crosstabs, split packed cells, merge name parts.

How — reshape to tall clean facts:

Vertical steps (one under another)

  1. Remove top junk rows, then Promote Headers
  2. Filter header/total repeats
  3. Unpivot month columns
  4. Split / Merge as needed

Tiny example: Jan/Feb columns, then City, Month, GMV tall table (fictional MH).

Ravindra Bagale's Tip

Friends, if months are columns, Unpivot before you model. Star schemas want tall facts.

Merged cells and multi-row headers

About this data: Fictional West region print report.

Why it happens: Excel report layout for printing — not a flat table.

Before (dirty)

Col1Col2
Region: West
CityGMV
Pune10

After (clean)

CityGMV
Pune10

Power Query steps (vertical):

  1. Use first rows carefully: Remove Top Rows until real header
  2. Promote Headers
  3. Unmerge in Excel first when possible — Power Query hates merged cells
  4. Fill Down if category labels sit in merged blocks
= Table.PromoteHeaders(Table.Skip(Source, 2))
Header rows repeated mid-file

About this data: Fictional stacked monthly exports.

Why it happens: Someone copied monthly blocks stacked with a fresh header each time.

Before (dirty)

CityGMV
Pune10
CityGMV
Nashik8

After (clean)

CityGMV
Pune10
Nashik8

Power Query steps (vertical):

  1. Filter rows where City <> "City"
  2. Or remove rows that match header text
= Table.SelectRows(Source, each [City] <> "City")
Totals and subtotal rows inside data

About this data: Fictional city GMV with Excel total.

Why it happens: Excel SUM at bottom gets imported as a data row, then double-counts.

Before (dirty)

CityGMV
Pune10
Nashik8
Total18

After (clean)

CityGMV
Pune10
Nashik8

Power Query steps (vertical):

  1. Filter out City = "Total" / "Subtotal" / "Grand Total"
  2. Never leave total rows in fact tables
= Table.SelectRows(Source, each not List.Contains({"Total","Subtotal","Grand Total"}, [City]))
Pivoted / crosstab layout — Unpivot

About this data: Fictional MH city month matrix.

Why it happens: Managers love months across columns; models need tall facts.

Before (dirty)

CityJanFeb
Pune1012
Nashik89

After (clean)

CityMonthGMV
PuneJan10
PuneFeb12
NashikJan8
NashikFeb9

Power Query steps (vertical):

  1. Select City
  2. Transform — Unpivot Other Columns
  3. Rename Attribute, then Month, Value, then GMV
  4. Set types
= Table.UnpivotOtherColumns(Source, {"City"}, "Month", "GMV")
Several values in one cell — Split

About this data: Fictional product tags.

Why it happens: People pack lists into one Excel cell with ; or ,

Before (dirty)

Tags
kirana;snacks;tea

After (clean)

Tags.1Tags.2Tags.3
kiranasnackstea

Power Query steps (vertical):

  1. Split Column by Delimiter (;)
  2. Or Split into rows if you need one tag per fact row
= Table.SplitColumn(Source, "Tags", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Tags.1","Tags.2","Tags.3"})
One value across columns — Merge

About this data: Fictional CRM contacts.

Why it happens: CRM exports first/last separately; label needs full name.

Before (dirty)

FirstLast
RaviPatil

After (clean)

FullName
Ravi Patil

Power Query steps (vertical):

  1. Select First + Last
  2. Merge Columns with space separator
  3. Or add custom column Text.Combine
= Table.AddColumn(Source, "FullName", each Text.Trim([First] & " " & [Last]), type text)

8. Units, currency mix, outliers, leading zeros, encoding

Why: Real warehouse and global files mix units and scripts.

What: Normalise units to one measure, split currency code, flag impossible values, keep PIN as Text, import UTF-8.

How — normalise measures and text safety:

Vertical steps (one under another)

  1. Parse number + unit, then one base unit
  2. Currency symbol, then code + amount
  3. Flag outliers to Errors query
  4. Text for IDs with leading zeros
  5. UTF-8 for Marathi/Hindi

Tiny example: 5kg and 5000 g, then 5 WeightKg; PIN 001234 stays text (fictional).

Ravindra Bagale's Tip

Friends, leading zeros die when type becomes number. Set Text first.

Units mixed in values (5kg, 5000 g)

About this data: Fictional grocery weights — Pune DC.

Why it happens: Warehouse staff type unit beside the number.

Before (dirty)

Weight
5kg
5000 g
2.5 KG

After (clean)

WeightKg
5
5
2.5

Power Query steps (vertical):

  1. Extract number with Text parsing
  2. Detect unit (kg vs g) and convert grams / 1000
  3. Store unit in a separate column if needed
  4. Type Decimal
= Table.AddColumn(Source, "WeightKg", each let t=Text.Lower(_), n=Number.From(Text.Select(t, {"0".."9","."})) in if Text.Contains(t,"g") and not Text.Contains(t,"kg") then n/1000 else n)
Currency symbols mixed (₹ $ £ €)

About this data: Fictional multi-currency training set (rates made up).

Why it happens: Global team pastes local currency into one column.

Before (dirty)

Amount
₹1200
$15
€10

After (clean)

AmountINRCurrency
1200INR
1250USD
900EUR

Power Query steps (vertical):

  1. Detect symbol, then Currency code column
  2. Strip symbol, then Decimal Amount
  3. Convert with a rates table (do not hard-code forever)
// detect then strip — rates join in next query
= Table.AddColumn(Source, "Currency", each if Text.Contains([Amount],"₹") then "INR" else if Text.Contains([Amount],"$") then "USD" else "EUR")
Outliers and impossible values

About this data: Fictional customer master.

Why it happens: Typos (age 250) or wrong century dates.

Before (dirty)

AgeOrderDate
2502099-01-01
322026-03-01

After (clean)

AgeOrderDateFlag
2502099-01-01CHECK
322026-03-01OK

Power Query steps (vertical):

  1. Add conditional column Flag when Age>120 or OrderDate>Date.From(DateTime.LocalNow())
  2. Filter Flags to an Errors query for humans
  3. Do not silently delete without a log
= Table.AddColumn(Source, "Flag", each if [Age]>120 or [OrderDate]>Date.From(DateTime.LocalNow()) then "CHECK" else "OK")
Leading zeros lost (PIN, IDs)

About this data: Fictional MH PIN list.

Why it happens: Excel / type detect turns PIN into number, then 001234 becomes 1234.

Before (dirty)

PIN
411001
001234

After (clean)

PIN
411001
001234

Power Query steps (vertical):

  1. Set column to Text BEFORE any number transform
  2. In Excel source, format column as Text or prefix '
  3. Pad with Text.PadStart if zeros already lost and length is known
= Table.TransformColumns(Source, {{"PIN", each Text.PadStart(Text.From(_), 6, "0"), type text}})
Special characters and encoding (Marathi / Hindi / emoji)

About this data: Fictional bilingual order notes — Pune.

Why it happens: CSV saved with wrong encoding turns मराठी into ???. Emojis may break old tools.

Before (dirty)

Note
पुणे ऑर्डर ✅
Pune order

After (clean)

Note
पुणे ऑर्डर ✅
Pune order

Power Query steps (vertical):

  1. Get Data — CSV — set File Origin to UTF-8 (65001)
  2. If already broken, re-export from source as UTF-8
  3. Keep Text type; do not strip scripts unless policy requires
// UI: CSV dialog, then File Origin = UTF-8

9. Keys, folders, JSON, PDF junk, time zones, fiscal year, ID typos

Why: Advanced dirt shows up when systems meet.

What: Map categories, find orphan keys, combine folders carefully, expand JSON, strip PDF junk, convert zones, build FY columns, flag bad IDs.

How — handle system dirt:

Vertical steps (one under another)

  1. Mapping table for categories
  2. Anti-join orphans
  3. Folder combine with stable columns
  4. Expand JSON
  5. Filter PDF page lines
  6. UTC, then IST
  7. Fiscal columns
  8. Regex/pattern flag typos

Tiny example: Order C9 missing in DimCustomer, then (missing customer); UTC morning, then IST afternoon (fictional).

Ravindra Bagale's Tip

Friends, orphan keys are a model smell. Find them with a left merge before you publish.

Inconsistent codes and categories

About this data: Fictional assortment categories.

Why it happens: Different teams invent their own category spellings.

Before (dirty)

Category
FMCG
fmcg
Fast Moving
Grocery

After (clean)

Category
FMCG
FMCG
FMCG
Grocery

Power Query steps (vertical):

  1. Build DimCategory mapping table
  2. Merge (left join) fact to map
  3. Expand Canonical category
  4. Send unmapped rows to Errors
= Table.NestedJoin(Source, {"Category"}, DimMap, {"Alias"}, "j", JoinKind.LeftOuter)
Missing keys between tables

About this data: Fictional star schema orphans.

Why it happens: Orders reference CustomerId not in DimCustomer — orphan facts.

Before (dirty)

OrderIdCustomerId
O1C9

After (clean)

OrderIdCustomerIdCustomerName
O1C9(missing customer)

Power Query steps (vertical):

  1. Merge Orders to DimCustomer
  2. Find null expanded names
  3. Fix source or add Unknown member
  4. Relationships need matching keys
= Table.NestedJoin(Orders, {"CustomerId"}, DimCustomer, {"CustomerId"}, "c", JoinKind.LeftOuter)
Many files in a folder with different columns

About this data: Fictional monthly folder — Nashik DC.

Why it happens: Each month CSV gained a new column; combine breaks.

Before (dirty)

Source.NameCityGMVNote
jan.csvPune10
feb.csvPune12promo

After (clean)

CityGMVMonth
Pune10jan
Pune12feb

Power Query steps (vertical):

  1. Get Data — Folder — Combine
  2. Use a sample file with the stable columns only
  3. Remove extra columns after combine
  4. Or append queries that each select the common set
// Folder combine then
= Table.SelectColumns(Combined, {"City","GMV","Source.Name"})
JSON / nested data

About this data: Fictional API extract.

Why it happens: API returns nested JSON; one cell holds a record.

Before (dirty)

payload
{"city":"Pune","gmv":10}

After (clean)

citygmv
Pune10

Power Query steps (vertical):

  1. Convert JSON to table
  2. Expand record/list columns
  3. Set types on expanded fields
= Table.ExpandRecordColumn(Table.TransformColumns(Source, {{"payload", Json.Document}}), "payload", {"city","gmv"})
PDF / web table junk

About this data: Fictional PDF scrape.

Why it happens: PDF/web scrape brings page footers and title lines.

Before (dirty)

Col1
Page 1 of 5
City GMV
Pune 10

After (clean)

CityGMV
Pune10

Power Query steps (vertical):

  1. Remove rows matching Page * of *
  2. Promote real header
  3. Split City/GMV if stuck together
  4. Prefer a proper CSV from the owner when possible
= Table.SelectRows(Source, each not Text.StartsWith([Col1], "Page "))
Time zones

About this data: Fictional event log.

Why it happens: Server stores UTC; business in Asia/Calcutta (UTC+05:30).

Before (dirty)

EventUtc
2026-03-04T09:00:00Z

After (clean)

EventIst
2026-03-04 14:30:00

Power Query steps (vertical):

  1. Keep UTC column
  2. Add IST = DateTimeZone.SwitchZone or +#duration(0,5,30,0)
  3. Never mix zones in one column without labels
= Table.AddColumn(Source, "EventIst", each DateTimeZone.RemoveZone(DateTimeZone.SwitchZone([EventUtc], 5.5)))
Fiscal year vs calendar year

About this data: Fictional Indian FY.

Why it happens: Indian companies often start fiscal year in April.

Before (dirty)

Date
2026-04-01

After (clean)

DateFiscalYear
2026-04-01FY26-27

Power Query steps (vertical):

  1. Mark Date table
  2. Add FiscalYear = if Month>=4 then Year else Year-1 (pattern)
  3. Use fiscal columns in visuals when finance asks FY
= Table.AddColumn(Date, "FiscalYear", each if Date.Month([Date])>=4 then "FY" & Text.End(Text.From(Date.Year([Date])),2) & "-" & Text.End(Text.From(Date.Year([Date])+1),2) else "FY" & Text.End(Text.From(Date.Year([Date])-1),2) & "-" & Text.End(Text.From(Date.Year([Date])),2))
Typos in IDs

About this data: Fictional order ids.

Why it happens: Letter l vs digit 1; repeated paste.

Before (dirty)

OrderId
ORD-1001
ORD-100l
ORD-1001

After (clean)

OrderIdFlag
ORD-1001OK
ORD-100lTYPO?
ORD-1001DUP

Power Query steps (vertical):

  1. Validate with a pattern (starts with ORD-, digits)
  2. Flag non-matching
  3. Distinct on clean IDs
  4. Fix at source when possible
= Table.AddColumn(Source, "Flag", each if Text.Length([OrderId])<>8 then "TYPO?" else "OK")

10. How dirty can data be? — end-to-end cleanup

Why: Mess stacks. Students need one full walk-through.

What: See the worked showcase below the tip — dirty excerpt, then clean fact, then M sketch.

How — follow the order in the showcase:

Vertical steps (one under another)

  1. Open the downloadable messy file in practice assets.
  2. Repeat the 11 vertical steps from the showcase.
  3. Compare your FactSales_Clean to the clean sample CSV.
  4. Write three rows you sent to Errors and why.

Tiny example: Duplicate ORD-1001 removed; ORD-100l flagged TYPO_ID; $15 future date sent to Errors (fictional).

Ravindra Bagale's Tip

Friends, how dirty can data be? Very. Your job is a calm checklist, not panic.

Why: Real files stack many problems in one sheet. You need a calm order of steps.

What: One fictional Pune Kirana Weekly export that is badly messy. We clean it top to bottom.

How — clean in this order:

  1. Remove title / page junk rows; promote real headers.
  2. Filter out mid-file header repeats and Total rows.
  3. Trim + Clean text columns; fix Bombay, then Mumbai; Title case cities.
  4. Replace NA/- placeholders with null.
  5. Strip ₹ and commas; brackets, then negative; type Decimal for GMV.
  6. Parse dates with English (India) locale; convert Excel serials.
  7. Set PIN / OrderId as Text; pad PIN to 6.
  8. Unpivot month columns if present; split tag lists.
  9. Flag outliers (GMV < 0 extreme, future dates) into Errors query.
  10. Left-merge to DimCustomer; label orphan keys.
  11. Name the final query FactSales_Clean; Close & Apply.

About this data: Fictional Pune kirana weekly export for learning. Brands and numbers are made up.

Dirty sheet (excerpt)
ABCDE
Pune Kirana Weekly — CONFIDENTIAL
OrderIdCityPINGMVOrderDate
ORD-1001 mumbai 400001₹1,20003/04/2026
ORD-1001Bombay400001₹1,20003/04/2026
ORD-100lPUNE411001(900)45321
ORD-1003NashikNA2,50004-Apr-2026
Total4,000
OrderIdCityPINGMVOrderDate
ORD-1004पुणे001234$152099-01-01
Clean fact (excerpt)
OrderIdCityPINGMV_INROrderDateFlag
ORD-1001Mumbai40000112002026-04-03OK
ORD-1003Nashik(missing)25002026-04-04OK
ORD-100lPune411001-9002024-01-30TYPO_ID
ORD-1004Pune001234CURRENCY+FUTURE — to Errors
// Sketch of the cleanup chain (fictional training query names)
let
  Source = Excel.Workbook(File.Contents("PuneKirana_Messy.xlsx"), true),
  Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
  SkipTitle = Table.Skip(Sheet, 1),
  Headers = Table.PromoteHeaders(SkipTitle, [PromoteAllScalars=true]),
  NoHeaderRepeats = Table.SelectRows(Headers, each [OrderId] <> "OrderId" and [OrderId] <> "Total"),
  TrimCity = Table.TransformColumns(NoHeaderRepeats, {{"City", Text.Trim}}),
  FixAlias = Table.ReplaceValue(TrimCity, "Bombay", "Mumbai", Replacer.ReplaceText, {"City"}),
  // ... continue: duplicates, types, locale dates, flags, merge
  Result = FixAlias
in
  Result

11. Column profiling and a data quality checklist

Why: You cannot clean what you do not see. Profiling shows errors, empty, and distinct counts before you model.

What: In Power Query: View — Column quality, Column distribution, Column profile. Quality = % valid / error / empty. Distribution = distinct / unique. Profile = stats for the selected column (first 1000 rows by default — change to entire data set when needed).

How — profile then checklist:

Vertical steps (one under another)

  1. View — tick Column quality, distribution, profile.
  2. Profiling may use first 1000 rows — switch to entire data set for final checks.
  3. Sort columns with highest error % or empty % first.
  4. Run the checklist below before Close & Apply.
  5. Keep an Errors query for rows that need a human.

Tiny example: GMV column quality shows 4% error after Decimal cast, then those rows still have ₹ symbols (fictional).

Ravindra Bagale's Tip

Friends, turn on Column quality on every new file. Two minutes now saves two hours later.

Data quality checklist (print me)

  1. Headers are one clean row (no merged titles).
  2. No Total / Subtotal rows in facts.
  3. No mid-file repeated headers.
  4. Text trimmed and cleaned; aliases mapped.
  5. Placeholders (NA/-) handled on purpose.
  6. Types set with correct locale.
  7. IDs / PIN kept as Text with leading zeros.
  8. Dates parsed; fiscal columns added if needed.
  9. Units and currencies normalised.
  10. Outliers flagged; Errors query saved.
  11. Keys match dimensions (no silent orphans).
  12. Column quality reviewed on entire data set.
  13. Query names clear: FactSales_Clean, DimCity, Errors_Sales.
  14. About this data note written for the next human.

Download practice files: PuneKirana_Messy.csv · PuneKirana_Messy.xlsx · PuneKirana_Clean_Sample.csv (fictional learning data).

Quiz (5 questions)

1. PIN codes and employee IDs should usually be which type?

2. 03/04/2026 with English (India) locale is usually:

3. Excel serial 45321 in a column most likely needs:

4. Months across columns (Jan, Feb, …) should become a tall table using:

5. Column quality in Power Query mainly shows:

Practice tasks (12) — hints only, no full answers

  1. Task. List 8 Power Query data types and one use each.
    Hint: section 2 table.
  2. Task. Explain why 03/04 is dangerous without locale.
    Hint: section 3.
  3. Task. Clean a City column with Trim, Clean, Bombay, then Mumbai.
    Hint: section 5.
  4. Task. Turn ₹1,200 and (900) into decimal numbers.
    Hint: sections 6 and 8.
  5. Task. Remove Total rows and repeated headers from a stacked sheet.
    Hint: section 7.
  6. Task. Unpivot Jan/Feb columns into Month + GMV.
    Hint: section 7 unpivot.
  7. Task. Keep PIN 001234 as Text with 6 digits.
    Hint: section 8 leading zeros.
  8. Task. Flag age 250 and date 2099 as CHECK.
    Hint: section 8 outliers.
  9. Task. Find orphan CustomerId with a left merge.
    Hint: section 9.
  10. Task. Convert a UTC timestamp to IST (+05:30).
    Hint: section 9 time zones.
  11. Task. Open PuneKirana_Messy and produce FactSales_Clean matching the sample.
    Hint: section 10 showcase + assets.
  12. Task. Turn on Column quality/distribution/profile and fill the checklist.
    Hint: section 11.
  13. Task. Write M or UI steps to split Tags by semicolon.
    Hint: section 7 split.
  14. Task. Interview answer: RLS vs dirty data — which do you fix first and why?
    Hint: clean data before security theatre.

Ravi Sir's cards (Excel card style)

Standalone cards — no tip or photo sections. QR points to this lesson. Download PNG to revise offline.

Interview Q&A: Why do data types decide if a report is true?
Download interview-card PNG
Comparison Card: Dirty export vs clean table
Download comparison-card PNG
Revision Card: Data quality checklist before Close and Apply
Download revision-card PNG

Think like a professional

In a real tenant, rights and sharing are controlled by Fabric / Power BI admins. Always follow your company policy. This lesson uses fictional people and numbers so you can practise the ideas safely.