Home Course home Curriculum Learner dashboard Skill tree Business projects Datasets Final project
🧹 Module 3 · Power Query & Data Transformation Beginner LESSON 17 / 72

Data Types, Columns, Duplicates & Row Filters

30 min 5 quiz questions 1 practical challenge 5 syllabus topics

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.

Lesson 17 of 72 · Power Query & Data Transformation 24%
01

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.

By the end of this lesson you will be able to
  • 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
02

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.

The rule for this lesson
Check the row count before and after every destructive step, and account for the difference. If you remove duplicates and the count drops by 20, you should be able to say why there were 20 duplicates. "It dropped by some amount" is not good enough — that is how real data gets deleted unnoticed.
03

Concept

Data types

Every column has a type. Power Query guesses on load; you should decide deliberately.

The types that matter
TypeUse forWatch out for
TextNames, codes, categories, anything with leading zerosPostcodes and product codes must stay text — typing them as numbers destroys leading zeros
Whole NumberCounts, quantities, yearsRounds anything with decimals, silently
Decimal NumberMoney, measurements, ratesFloating point. Fine for almost everything
Fixed Decimal NumberCurrency where exact arithmetic mattersFour decimal places, exact. Use for financial reconciliation
DateDates with no time componentMuch better than Date/Time for model size — see below
Date/TimeTimestamps where the time mattersHigh cardinality — compresses badly and bloats the model
True/FalseGenuine booleansUse it rather than "Yes"/"No" text where you can
Split Date/Time unless you need the time
A 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:

  1. It is applied before you remove columns, so it types columns you are about to delete — wasted work.
  2. 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

Two ways
MethodBehaviourWhich to use
Remove ColumnsNames the columns to removeA new column at source flows through automatically. Good when you want new columns
Choose ColumnsNames the columns to keepUsually better. New columns at source are ignored; a missing required column errors loudly
Removing is not hiding
Remove in Power Query means the column never enters the model — it is not loaded, not stored, and does not count toward file size.

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.
The second form deletes data you may need
If two rows share a CustomerID but differ in address, deduplicating on CustomerID keeps one address and silently discards the other. Whether that is correct depends entirely on why the duplicate exists — and "first occurrence" is decided by row order, which may be arbitrary.

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

  1. Reference the query (right-click → Reference) so you can investigate without altering the main one.
  2. Group By the key column, with an aggregation of Count Rows.
  3. Filter to count > 1.
  4. 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 types and their fragility
FilterFragilityBetter alternative
Selecting values from the checkbox listHigh — it becomes a hard-coded list. New values are excluded silentlyUse a condition instead: does not equal, does not contain
Text does not contain 'TEST'Low — a rule, not a listGood as is
Date is after 2024-01-01Medium — the hard-coded date will need updatingUse a relative filter, or a parameter
Value is not nullLowGood as is
The checkbox filter trap
Selecting values from the dropdown list produces M like 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.
04

Visual Explanation

Step through the cleaning on the real messy file. Each step shows the actual data and what changed.

Row count accounting

Every destructive step, accounted for
StepRows beforeRows afterDifferenceExplained?
Source—280—Matches source file
Choose columns2802800Columns, not rows
Remove exact duplicates280266−14Yes — 14 identical rows from a double-export
Trim and clean2662660Non-destructive
Filter test accounts2662660Yes — none present
This table is the deliverable
Keep it as you build. It is the cleaning log Module 14 asks for, and it is what lets you answer "why does your customer count differ from the source?" with a specific number and a reason rather than a shrug.
05

Power BI Demonstration

Load and profile first
As Lesson 1. Record the starting row count: 280.
Delete the automatic Changed Type step
Select it in Applied Steps and click the ×. You will set types deliberately, later, on the columns you actually keep.
Choose the columns to keep
Home › Choose Columns. Tick only what you need. Note this names what to keep, which is the more robust direction.
Check for exact duplicates
Home › Remove Rows › Remove Duplicates with no column selected. Check the row count. It should drop by 14.
Investigate the remaining key duplicates
Right-click the query → Reference. On the reference: Transform › Group By, group by CustomerID, aggregate Count Rows. Filter to count > 1. You will find the remaining 6.
Look at those duplicates properly
Merge the grouped result back to the main query on CustomerID to see the full rows. Are they identical apart from case? Then they are safe. Do they differ meaningfully? Then escalate — do not guess.
Trim and clean the text columns
Select the text columns → Transform › Format › Trim, then Clean. Trim removes leading and trailing whitespace; Clean removes non-printable characters.
Write a rule-based filter
Instead of ticking values, use the column dropdown → Text Filters › Does Not Contain and enter your term. Check the formula bar to see the rule it produced.
Compare against a checkbox filter
Now try filtering by ticking values instead. Look at the formula bar. Note the hard-coded list — and imagine a new value appearing next month.
Rename every step
Go through Applied Steps and rename each one to describe intent.
Record the final row count
And account for every row lost since the source.
HomeRemove RowsRemove Duplicates
06

Example Dataset

The same file as Lesson 1. If you already have it loaded, continue from there.

bwb-messy-customers.csv
26 KB · 280 rows · 8 columns · CSV
Business context
A deliberately dirty extract — the kind of file that actually lands in your inbox. It combines two exports, so it contains duplicate rows and duplicate customer IDs. Country is spelled 25 different ways for 6 real countries. Dates use five different formats. Revenue mixes currency symbols, thousands separators and blanks. Status has 7 spellings for 3 real states. Emails are missing, malformed or padded with whitespace. Every Power Query lesson uses this file.

Columns & data types

ColumnData typeMeaning
CustomerIDTextIntended to be unique — it is not. Duplicates are your first clue.
FullNameTextSome entries have double spaces, trailing spaces, or ALL CAPS.
EmailTextBlank in some rows, malformed in others, whitespace-padded in others.
CountryText25 distinct spellings for 6 real countries.
SignupDateTextLoads as text because five date formats are mixed together.
LifetimeRevenueTextLoads as text: currency symbols and thousands separators present.
PhoneTextFour different formatting conventions.
StatusText7 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?
07

Step-by-Step Exercise

Start clean
Load the messy file fresh. Delete the automatic Changed Type step.
Keep only what you need
Use Choose Columns to keep CustomerID, FullName, Email, Country, SignupDate, LifetimeRevenue and Status. Drop Phone.
Remove exact duplicates and record the drop
No column selected. Note the before and after counts.
Investigate the key duplicates without removing them
Use the Reference + Group By technique. Write down how many remain and whether they look identical or conflicting.
Trim and clean all text columns
Then re-check the distinct count on Country. It should fall — trailing spaces were creating false distinctness.
Try to set types, and observe the failure
Attempt Decimal Number on LifetimeRevenue and Date on SignupDate. Record how many errors each produces. Do not fix them — Lesson 3 does that.
Write two filters for the same intent
"Exclude churned customers", once by ticking values and once as a rule. Compare the formula bar for each, and write one sentence on which you would ship.
Produce the row-count accounting table
Every step, rows before, rows after, and the explanation.
08

Expected Result

Expected results
CheckExpected
Starting rows280
After removing exact duplicates266 — a drop of 14
Remaining duplicate CustomerIDs6 — the near-duplicates where the name is uppercased
Country distinct count after TrimFalls from 25 — trailing spaces were inflating it
Errors typing LifetimeRevenue as DecimalMany — the values with $ and thousands separators
Errors typing SignupDate as DateMany — four of the five formats fail under any one locale
Checkbox filter formulaA hard-coded or list of specific values
Rule filter formulaA single condition such as each [Status] <> "Churned"
The Trim result is a small revelation
Trimming reduces the Country distinct count before you write a single mapping rule, because 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

Power Query M · Checkbox filter — fragile
Table.SelectRows(Source, each
    [Status] = "Active"
 or [Status] = "active"
 or [Status] = "ACTIVE"
 or [Status] = "Inactive"
 or [Status] = "inactive"
)
Power Query M · Rule filter — robust
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.

09

Common Mistakes

Deduplicating on a key column without looking at the duplicates first
Power Query keeps the first occurrence by row order, which may be arbitrary. If the rows differ meaningfully, you have silently chosen one and destroyed the other.

Fix: Reference the query, Group By the key, filter to count > 1, and inspect. Two minutes, and it tells you whether removal is safe.
Filtering by ticking values in the dropdown
Produces a hard-coded list. New values appearing at source are silently excluded, taking their rows with them.

Fix: Write the filter as a rule expressing your actual intent — does not equal, does not contain.
Leaving the automatic Changed Type step at the top
It types columns you are about to delete, and it errors if any source column is renamed — even one you never use.

Fix: Delete it. Remove columns first, then set types deliberately on what remains.
Using Date/Time when Date would do
Enormously higher cardinality, which compresses badly and inflates the model.

Fix: Use Date unless you genuinely need the time. If you do, split into separate Date and Time columns.
Not accounting for row-count changes
Rows disappear across several steps and nobody can say how many or why. The customer count differs from the source and there is no explanation.

Fix: Record rows before and after every destructive step, and explain each difference. Keep the table.
10

Professional Tip

Order your query: reduce, then clean, then type
A well-structured query follows the same shape every time:

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 _Raw reference query with load disabled. When a count looks wrong, you can compare against untransformed data without leaving the file.
11

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?

12

Practical Challenge

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.

Requirements
  • 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.

13

Solution

Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.

The query

Power Query M · Cleaned query — reduce then clean
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

StepBeforeAfterΔExplanation
Source—280—Matches the source file's data rows
Keep required columns2802800Columns only
Remove exact duplicate rows280266−14Rows identical across all seven columns
Exclude test accounts2662660None present in this file; the rule is defensive
Trim / Clean / Collapse2662660Non-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:

Sample of the remaining duplicates
CustomerIDFullNameEmailCountryRevenue
CUST0112Hassan Khanhassan.khan@mail.coUK22140.55
CUST0112HASSAN KHANhassan.khan@mail.coUK22140.55
The decision, and why
Finding: in every one of the 6 cases, the rows are identical in all columns except that the name is uppercased in one of them. Email, country, revenue and status all match exactly.

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.

14

Lesson Summary

Key takeaways
  • 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

Changing data typesRemoving columnsRemoving duplicatesFiltering rowsLocale-aware type changes
15

Progress

Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.

Lesson complete — 24% of Power BI Mastery done
Progress saved. Keep the momentum: the next lesson builds directly on this one.