Data Types, Columns, Duplicates & Row Filters
The first real cleaning. Data types set deliberately and locale-correctly, columns you do not need removed, duplicates dealt with safely, and rows filtered without silently losing data. These four operations account for the majority of Power Query work in most projects.
Learning Objective
Each of these operations is a single click and each has a way of going quietly wrong. This lesson is about doing them deliberately rather than reflexively.
- Set data types explicitly, with the correct locale, and know why order matters
- Remove columns safely, and explain why removing beats hiding
- Deduplicate without destroying information, and know when not to
- Filter rows in a way that survives changes to the source data
- Validate after every destructive operation
Why This Matters
All four of these operations are destructive — they change what reaches the model, and downstream you cannot tell what was removed. That is exactly why they deserve care.
The specific danger is that each one fails silently. A wrong type produces errors that become nulls that become understated totals. A careless deduplication deletes real rows. A row filter written against today's data excludes valid rows tomorrow. Nothing warns you.
Concept
Data types
Every column has a type. Power Query guesses on load; you should decide deliberately.
| Type | Use for | Watch out for |
|---|---|---|
| Text | Names, codes, categories, anything with leading zeros | Postcodes and product codes must stay text — typing them as numbers destroys leading zeros |
| Whole Number | Counts, quantities, years | Rounds anything with decimals, silently |
| Decimal Number | Money, measurements, rates | Floating point. Fine for almost everything |
| Fixed Decimal Number | Currency where exact arithmetic matters | Four decimal places, exact. Use for financial reconciliation |
| Date | Dates with no time component | Much better than Date/Time for model size — see below |
| Date/Time | Timestamps where the time matters | High cardinality — compresses badly and bloats the model |
| True/False | Genuine booleans | Use it rather than "Yes"/"No" text where you can |
Date/Time column has as many distinct values as there are seconds in your data — potentially millions. A Date column has at most 365 per year.Since VertiPaq compresses by distinct value, this single change can dramatically reduce model size. If you need the time, split it into a separate Time column — two low-cardinality columns compress far better than one high-cardinality one. Module 11 Lesson 2 covers why.
Change Type versus Change Type Using Locale
Covered in Module 2, and worth repeating because it is the commonest silent corruption:
- Change Type uses the file's regional setting. Convenient, and it embeds an invisible assumption.
- Change Type Using Locale makes you state the source's locale explicitly. More verbose, and it documents the assumption where the next person can see it.
Where the type step goes
Power Query adds a Changed Type step automatically on load, covering every column. Two problems with leaving it there:
- It is applied before you remove columns, so it types columns you are about to delete — wasted work.
- If a column is renamed at source, the step errors referring to a column that no longer exists — even if you never used it.
The professional pattern: remove columns first, then set types on what remains. Some practitioners delete the automatic step entirely and type explicitly at the end.
Removing columns
| Method | Behaviour | Which to use |
|---|---|---|
| Remove Columns | Names the columns to remove | A new column at source flows through automatically. Good when you want new columns |
| Choose Columns | Names the columns to keep | Usually better. New columns at source are ignored; a missing required column errors loudly |
Hide in the model means it is loaded and stored but not shown in the Data pane.
For file size and refresh speed, only removing helps. For usability, hiding is the right tool. They solve different problems.
Removing duplicates
Home › Remove Rows › Remove Duplicates, or right-click a column header. What it does depends on what is selected:
- No column selected — removes rows where every column matches. Safe: these are genuinely identical rows.
- One or more columns selected — removes rows where those columns match, keeping the first occurrence and discarding the rest.
Before deduplicating on a subset of columns, look at the duplicates first. Group by the key, filter to counts greater than one, and inspect them.
Investigating duplicates properly
- Reference the query (right-click → Reference) so you can investigate without altering the main one.
- Group By the key column, with an aggregation of Count Rows.
- Filter to count > 1.
- Merge that back to the original to see the full duplicate rows.
This takes two minutes and tells you whether the duplicates are identical (safe to remove) or conflicting (a source problem to escalate).
Filtering rows
Filtering is where silent data loss most often originates, because a filter written against today's data may exclude valid rows tomorrow.
| Filter | Fragility | Better alternative |
|---|---|---|
| Selecting values from the checkbox list | High — it becomes a hard-coded list. New values are excluded silently | Use a condition instead: does not equal, does not contain |
| Text does not contain 'TEST' | Low — a rule, not a list | Good as is |
| Date is after 2024-01-01 | Medium — the hard-coded date will need updating | Use a relative filter, or a parameter |
| Value is not null | Low | Good as is |
each ([Status] = "Active" or [Status] = "Inactive"). It works perfectly — until a new status appears next month, which is then silently excluded along with all its rows.If your intent is "exclude cancelled", write that:
each [Status] <> "Cancelled". Then new statuses flow through, which is almost always what you meant.Visual Explanation
Step through the cleaning on the real messy file. Each step shows the actual data and what changed.
Row count accounting
| Step | Rows before | Rows after | Difference | Explained? |
|---|---|---|---|---|
| Source | — | 280 | — | Matches source file |
| Choose columns | 280 | 280 | 0 | Columns, not rows |
| Remove exact duplicates | 280 | 266 | −14 | Yes — 14 identical rows from a double-export |
| Trim and clean | 266 | 266 | 0 | Non-destructive |
| Filter test accounts | 266 | 266 | 0 | Yes — none present |
Power BI Demonstration
Example Dataset
The same file as Lesson 1. If you already have it loaded, continue from there.
bwb-messy-customers.csv26 KB · 280 rows · 8 columns · CSV
Columns & data types
| Column | Data type | Meaning |
|---|---|---|
CustomerID | Text | Intended to be unique — it is not. Duplicates are your first clue. |
FullName | Text | Some entries have double spaces, trailing spaces, or ALL CAPS. |
Email | Text | Blank in some rows, malformed in others, whitespace-padded in others. |
Country | Text | 25 distinct spellings for 6 real countries. |
SignupDate | Text | Loads as text because five date formats are mixed together. |
LifetimeRevenue | Text | Loads as text: currency symbols and thousands separators present. |
Phone | Text | Four different formatting conventions. |
Status | Text | 7 spellings for 3 real states (Active, Inactive, Churned). |
Questions this dataset can answer
- How many genuinely distinct customers are in this file?
- What is total lifetime revenue once the column is numeric?
- How many customers are Active once status is standardised?
- Which rows should be rejected rather than repaired, and why?
Step-by-Step Exercise
Expected Result
| Check | Expected |
|---|---|
| Starting rows | 280 |
| After removing exact duplicates | 266 — a drop of 14 |
| Remaining duplicate CustomerIDs | 6 — the near-duplicates where the name is uppercased |
| Country distinct count after Trim | Falls from 25 — trailing spaces were inflating it |
| Errors typing LifetimeRevenue as Decimal | Many — the values with $ and thousands separators |
| Errors typing SignupDate as Date | Many — four of the five formats fail under any one locale |
| Checkbox filter formula | A hard-coded or list of specific values |
| Rule filter formula | A single condition such as each [Status] <> "Churned" |
australia and australia were counted separately.It is a good demonstration of a general principle: do the cheap, safe, universal cleaning first. Trim and Clean cost nothing, lose nothing, and reduce the problem you then have to solve deliberately.
The two filters compared
Table.SelectRows(Source, each
[Status] = "Active"
or [Status] = "active"
or [Status] = "ACTIVE"
or [Status] = "Inactive"
or [Status] = "inactive"
)Table.SelectRows(Source, each
Text.Lower([Status]) <> "churned"
)The first excludes any status not on the list — including new ones, and including Churned spelled differently. The second states the actual intent and handles case at the same time.
Common Mistakes
Fix: Reference the query, Group By the key, filter to count > 1, and inspect. Two minutes, and it tells you whether removal is safe.
Fix: Write the filter as a rule expressing your actual intent — does not equal, does not contain.
Fix: Delete it. Remove columns first, then set types deliberately on what remains.
Fix: Use Date unless you genuinely need the time. If you do, split into separate Date and Time columns.
Fix: Record rows before and after every destructive step, and explain each difference. Keep the table.
Professional Tip
1. Reduce — filter rows, choose columns. Get the table as small as possible first, so everything after operates on less data (and, on a database, so it folds).
2. Clean — trim, clean, replace values, handle nulls. Fix the content.
3. Type — set data types last, on clean values that will actually convert.
4. Enrich — add columns, merge, group. Build on a foundation you trust.
Typing before cleaning is the single most common structural mistake, and it is why the simulator above shows the type step failing. Clean values convert; dirty ones error.
Two supporting habits:
- Prefer Choose Columns over Remove Columns. Naming what you keep makes the query's requirements explicit and makes it tolerant of new source columns.
- Keep a
_Rawreference query with load disabled. When a count looks wrong, you can compare against untransformed data without leaving the file.
Knowledge Check
Knowledge Check
5 questions. Answer each one to see why the right answer is right.
You select the CustomerID column and click Remove Duplicates. What happens to rows sharing an ID but differing in address?
Why should data types be set after cleaning values rather than before?
What is the practical difference between Remove Columns and Choose Columns?
You filter Status by ticking "Active" and "Inactive" in the dropdown. Next month a new status "Suspended" appears. What happens?
Why prefer a Date column over Date/Time when the time is not needed?
Practical Challenge
Clean the customer file, and defend every destructive decision
Build a query on bwb-messy-customers.csv that performs only the operations from this lesson — choose columns, remove duplicates, trim and clean, filter rows. Do not fix values or types yet. Then write a defence of every step that removed data.
- Steps ordered as reduce → clean, with the automatic type step deleted
- Every step renamed to describe intent
- A row-count accounting table covering every destructive step
- For the key duplicates: evidence of what you found, and a decision with justification
- At least one filter written as a rule, with the M shown
- One sentence per destructive step defending it to someone who asks "why is your row count lower than the source?"
For the key duplicates, the investigation matters more than the decision. Use Reference → Group By CustomerID → Count Rows → filter count > 1, then merge back to see the full rows.
Look carefully at what distinguishes them. If the only difference is the case of the name, that tells you something specific about how the duplicate arose — and it makes the decision easy.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
The query
let
Source =
Csv.Document(File.Contents(DataFolder & "bwb-messy-customers.csv"),
[Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
#"Promote headers" =
Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
// ---- 1. REDUCE ------------------------------------------------
#"Keep required columns" =
Table.SelectColumns(#"Promote headers",
{"CustomerID", "FullName", "Email", "Country",
"SignupDate", "LifetimeRevenue", "Status"},
MissingField.Error),
#"Remove exact duplicate rows" =
Table.Distinct(#"Keep required columns"),
#"Exclude test accounts" =
Table.SelectRows(#"Remove exact duplicate rows", each
not Text.Contains(Text.Upper([FullName]), "TEST")
and not Text.Contains(Text.Upper([Email]), "TEST@")),
// ---- 2. CLEAN -------------------------------------------------
#"Trim whitespace" =
Table.TransformColumns(#"Exclude test accounts",
{{"FullName", Text.Trim, type text},
{"Email", Text.Trim, type text},
{"Country", Text.Trim, type text},
{"Status", Text.Trim, type text}}),
#"Remove non-printable characters" =
Table.TransformColumns(#"Trim whitespace",
{{"FullName", Text.Clean, type text},
{"Email", Text.Clean, type text}}),
#"Collapse double spaces in names" =
Table.TransformColumns(#"Remove non-printable characters",
{{"FullName", each Text.Replace(_, " ", " "), type text}})
in
#"Collapse double spaces in names"Row-count accounting
| Step | Before | After | Δ | Explanation |
|---|---|---|---|---|
| Source | — | 280 | — | Matches the source file's data rows |
| Keep required columns | 280 | 280 | 0 | Columns only |
| Remove exact duplicate rows | 280 | 266 | −14 | Rows identical across all seven columns |
| Exclude test accounts | 266 | 266 | 0 | None present in this file; the rule is defensive |
| Trim / Clean / Collapse | 266 | 266 | 0 | Non-destructive |
The key duplicates — evidence and decision
After removing exact duplicates, 6 CustomerIDs still appear more than once. The investigation query shows what distinguishes them:
| CustomerID | FullName | Country | Revenue | |
|---|---|---|---|---|
| CUST0112 | Hassan Khan | hassan.khan@mail.co | UK | 22140.55 |
| CUST0112 | HASSAN KHAN | hassan.khan@mail.co | UK | 22140.55 |
Conclusion: these are the same customer, exported twice, with one system upper-casing the name field. They are not two different people sharing an ID.
Decision: safe to deduplicate — but not yet. If I deduplicate now, Power Query keeps whichever row happens to come first, which may be the ALL-CAPS one. The correct order is to normalise the name casing first (Lesson 3), which turns these into exact duplicates that
Table.Distinct removes safely and deterministically.This is why the cleaning order matters, and it is the kind of thing that only becomes visible if you look at the duplicates rather than just removing them.
Defending each destructive step
- Remove exact duplicate rows (−14): "Fourteen rows were byte-identical to another row across every column. That pattern is characteristic of an export running twice rather than of fourteen real coincidences, and keeping them would double-count those customers' revenue."
- Exclude test accounts (0): "This filter removed nothing from the current file. It is written as a rule so that if a test account appears in a future export it is excluded automatically rather than quietly inflating the customer count."
- Key duplicates (deferred): "Six customer IDs still repeat. I have confirmed they are casing variants of the same record, not different people, and I will remove them after normalising the name field so the row that survives is deterministic rather than dependent on row order."
What makes this answer strong
Two things. First, every removed row is accounted for with a reason, not just a count — so the question "why is your number lower than the source?" has a specific, checkable answer.
Second, and more importantly, it defers the key deduplication rather than doing it now. Deduplicating before normalising the case would work, and would non-deterministically keep whichever row came first. Recognising that ordering problem — and choosing to wait — is the difference between a query that happens to produce the right answer and one that is designed to.
Lesson Summary
- Order every query as reduce → clean → type → enrich. Typing before cleaning is the commonest structural mistake.
- Delete the automatic Changed Type step. Set types deliberately, later, on the columns you keep.
- Choose Columns beats Remove Columns — it names what you keep and fails loudly on a missing requirement.
- Remove Duplicates with no column selected removes only fully identical rows and is safe. With a column selected it keeps the first by row order and discards the rest.
- Investigate duplicates before removing them — Reference, Group By, count > 1.
- Write filters as rules, not ticked lists. A ticked list silently excludes new values.
- Use Date rather than Date/Time unless you need the time — cardinality drives model size.
- Account for every row-count change. Keep the table; it is your cleaning log.
Syllabus topics delivered
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.