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

Replacing Values, Nulls & Quality Checks

29 min 5 quiz questions 1 practical challenge 4 syllabus topics

Standardising inconsistent values is the work that turns technically-valid data into genuinely usable data. This lesson covers replacing values at scale with a mapping table rather than by hand, dealing honestly with nulls, and building a data-quality check query that tells you when the source changes.

Lesson 18 of 72 · Power Query & Data Transformation 25%
01

Learning Objective

Twenty-five spellings of six countries is not fixed by twenty-five Replace Values steps. This lesson covers the approach that scales and, more importantly, the approach that notices when something new appears.

By the end of this lesson you will be able to
  • Distinguish null, blank and empty string, and handle each appropriately
  • Standardise inconsistent values using a mapping table rather than repeated Replace steps
  • Choose deliberately between removing, replacing and flagging incomplete rows
  • Build a data-quality check query that fails loudly on unexpected values
  • Explain why fixing a value is sometimes the wrong answer
02

Why This Matters

Take the country problem literally. The file has 25 spellings of 6 countries. Someone reports revenue by country. The United Kingdom's revenue is split across United Kingdom, UK, U.K., england, Great Britain and  United Kingdom  — six rows in a bar chart where there should be one, each showing a fraction of the real figure.

The chart is not broken. It renders perfectly. It is simply and completely wrong, and it looks exactly like a correct chart.

The scaling problem
You could fix this with 25 individual Replace Values steps. It would work today. Next month a new spelling arrives — Britain, or GB — and it flows straight through unmapped, appearing as a new bar nobody expected.

The fix that scales is a mapping table plus a check that flags anything unmapped. That second part is what most people leave out, and it is what turns a one-off clean into a pipeline.
03

Concept

Null, blank and empty string

These look the same in the preview and behave differently. Getting them straight prevents a lot of confusion.

Three kinds of nothing
What it isIn Power QueryIn DAX
nullNo value at all — the absence of dataShown as null in italicsBLANK()
empty stringA text value of zero lengthShown as an empty cell, but it is a valueAn empty string, not blank
whitespaceA string containing only spacesLooks empty; is notA non-empty string
Why this matters practically
Table.SelectRows(t, each [Email] <> null) keeps rows where Email is an empty string, because an empty string is not null.

Column quality's Empty percentage counts nulls and empty strings together, which is helpful for spotting the problem and unhelpful for diagnosing it.

The reliable move: convert empty strings and whitespace to genuine nulls early, so you only have one kind of nothing to reason about.
Power Query M · Normalise all the nothings to null
Table.TransformColumns(Source, {
    {"Email",   each if Text.Trim(_ ?? "") = "" then null else Text.Trim(_), type text},
    {"Country", each if Text.Trim(_ ?? "") = "" then null else Text.Trim(_), type text}
})

The ?? operator is null-coalescing — it returns the left side unless it is null, in which case the right. It stops Text.Trim erroring on a value that is already null.

Three things you can do with a null

And when each is right
OptionWhen it is rightWhen it is wrong
Remove the rowThe row is unusable without that field — a sales row with no amountThe field is incidental. Removing a customer because their email is missing loses their revenue too
Replace with a valueThere is a genuine, defensible default — Discount null means 0You are inventing data. Replacing a missing revenue with 0 understates the total and looks like a real zero
Flag and keepUsually. Keep the row, add a flag column, let the report decideRarely wrong. Slightly more work
Default to flagging
Removing loses information. Replacing invents information. Flagging preserves both the data and the fact that something was missing, and pushes the decision to the report — where it belongs, because different questions want different treatment.

A Has Email true/false column costs almost nothing and lets one visual exclude them while another counts them.

Standardising values at scale

The wrong way

Right-click → Replace Values, 25 times. It works. It produces 25 steps nobody will read, it is invisible to anyone auditing the query, and it silently ignores anything new.

The right way — a mapping table

  1. Create a two-column table: Raw and Standard.
  2. Merge it onto your data on the raw value, using a left outer join.
  3. Expand the Standard column.
  4. Check for nulls in Standard — those are values not in your mapping.
  5. Replace the original column with the standardised one.

Step 4 is the one that matters. A null in Standard means a raw value arrived that your mapping does not know about — which is exactly the event you want to be told about.

Where the mapping table lives

OptionGoodLess good
Enter Data in Power QuerySelf-contained; travels with the fileEditing it means opening Power Query
A CSV or Excel fileA non-technical colleague can maintain itAnother file to keep track of
A database tableShared across every report that needs itNeeds a DBA, and a change process

For a small, stable mapping, Enter Data is fine. For something a business user should own — a product category mapping, say — a spreadsheet they can edit is much better than asking them to raise a ticket with you.

Text standardisation shortcuts

Before reaching for a mapping table, apply the free transformations. They often reduce the problem substantially:

TransformationEffectWhere
TrimRemoves leading and trailing whitespaceTransform › Format › Trim
CleanRemoves non-printable charactersTransform › Format › Clean
lowercaseCase-folds — turns Active/active/ACTIVE into one valueTransform › Format › lowercase
Capitalize Each WordProper-cases namesTransform › Format › Capitalize Each Word
Capitalize Each Word is not safe on all names — it produces Mcdonald, O'brien and Van Der Berg. For display names it is usually acceptable; for anything used as a key it is not. Lowercase for matching, and preserve the original for display.

Building a data-quality check query

This is the part that turns cleaning into a pipeline. A check query does not feed the model — it exists to tell you when something changed.

The pattern: reference your cleaned query, filter to the rows that should not exist, and load it. If it ever returns rows, something has changed at source.

Power Query M · A data-quality check query
// Returns rows ONLY when something is wrong.
// An empty result means the source behaved as expected.
let
    Cleaned = #"Customers Cleaned",

    UnmappedCountry =
        Table.SelectRows(Cleaned, each [Country Standard] = null),

    UnmappedStatus =
        Table.SelectRows(Cleaned, each [Status Standard] = null),

    DuplicateKeys =
        Table.SelectRows(
            Table.Group(Cleaned, {"CustomerID"}, {{"n", Table.RowCount, Int64.Type}}),
            each [n] > 1),

    Issues = Table.Combine({
        Table.AddColumn(UnmappedCountry, "Issue", each "Unmapped country",  type text),
        Table.AddColumn(UnmappedStatus,  "Issue", each "Unmapped status",   type text),
        Table.AddColumn(DuplicateKeys,   "Issue", each "Duplicate key",     type text)
    })
in
    Issues

Load that query and put a card on a hidden validation page showing COUNTROWS of it. If it is ever above zero, you know before anyone else does.

04

Visual Explanation

Step through the standardisation on real data.

What the mapping table looks like

Country mapping (extract)
RawStandard
usUnited States
USAUnited States
U.S.A.United States
United States of AmericaUnited States
UKUnited Kingdom
U.K.United Kingdom
englandUnited Kingdom
Great BritainUnited Kingdom
AUSAustralia
australiaAustralia
CACanada
canadaCanada
DEGermany
deutschlandGermany
PAKPakistan
pakistanPakistan
Lowercase the join key
Build the mapping on lowercased, trimmed raw values and join on the lowercased column. That way UK, uk and  Uk  all match one mapping row, and your table stays a fraction of the size it would otherwise need to be.
05

Power BI Demonstration

Continue from Lesson 2
Your query should be reduced, deduplicated and trimmed.
Convert empty strings to null
Select a text column → Transform › Replace Values. Leave Value to find empty and enter null in Replace with. Tick Advanced options › Match entire cell contents.
Lowercase Status and see the effect
Select Status → Transform › Format › lowercase. Check the distinct count in column distribution — 7 becomes 3.
Build the mapping table
Home › Enter Data. Two columns, Raw and Standard. Enter the pairs. Name the query Country Mapping.
Add a lowercase join key to both sides
On your main query, add a custom column Text.Lower(Text.Trim([Country])). Do the same on the mapping table's Raw column.
Merge
Home › Merge Queries. Select your data as the first table and the mapping as the second, join on the lowercase key columns, and choose Left Outer.
Expand and check for nulls
Expand the merged column, keeping only Standard. Then filter that column to is null and see how many rows have an unmapped country. It should be zero — if not, your mapping is incomplete.
Remove the filter and the helper columns
Delete the filter step, then remove the lowercase key column and the original Country column, keeping only the standardised one. Rename it to Country.
Strip revenue formatting
Select LifetimeRevenue → Replace Values: find $, replace with nothing. Repeat for ,. Now the column will type cleanly.
Add the Has Email flag
Add Column › Conditional Column. If Email is null then FALSE else TRUE. Set the type to True/False.
Build the check query
Right-click your cleaned query → Reference. Name it DQ - Unmapped Values. Filter it to rows where the standardised country is null. Load it.
Rename every step
As always.
HomeMerge QueriesLeft OuterExpand
Test the check query by breaking something
Add a row to your source with a country spelled in a way your mapping does not cover. Refresh. The check query should return that row. Then remove it.

A check you have never seen fire is a check you do not know works.
06

Example Dataset

Continuing with the same file. If you have Lesson 2's query, build on it.

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

Normalise the nothings
Convert empty strings and whitespace-only values to null across every text column.
Apply the free transformations first
Trim, Clean, and lowercase Status. Record the distinct count of Country and Status before and after.
Build both mapping tables
One for Country (25 raw values → 6 standard), one for Status (7 → 3).
Merge both, with left outer joins
And verify zero nulls in each standardised column.
Deliberately break the mapping
Remove one entry from your country mapping and refresh. Confirm the null appears and that you can identify which raw value caused it. Then restore it.
Handle the missing emails three ways
Build three variants: one removing those rows, one replacing with a placeholder, one adding a flag. Compare the resulting customer count and total revenue for each.
Build the check query
Covering unmapped countries, unmapped statuses, and duplicate CustomerIDs. Confirm it returns zero rows on clean data.
Write down which null strategy you would ship, and why
One paragraph. This is the judgement the lesson is really testing.
08

Expected Result

Expected results
CheckExpected
Country distinct, before standardising25
Country distinct, after mapping6
Status distinct, after lowercase alone3 — no mapping table needed
Nulls in Country Standard0, if the mapping is complete
Check query row count on clean data0
Check query after removing a mapping entryThe rows carrying that raw value

The three null strategies compared

StrategyCustomersTotal revenueProblem
Remove rows with no email28 fewerUnderstatedYou deleted real customers and their real revenue to fix a contactability problem
Replace email with a placeholderUnchangedCorrectInvented data. "unknown@example.com" looks like an address and will eventually be emailed
Flag and keepUnchangedCorrectNone. Both facts preserved; the report decides
The revenue column makes the case
Removing the 28 customers with no email also removes their lifetime revenue from every total. The email gap is a contactability problem; deleting the rows converts it into a revenue accuracy problem, which is far worse and much harder to notice.

This is the general argument for flagging: it keeps a local problem local.
09

Common Mistakes

Fixing values with repeated Replace Values steps
Twenty-five steps nobody will read, and a new spelling next month passes through unmapped and unnoticed.

Fix: One mapping table plus a merge, plus a null check on the result. Scales, and it tells you when something new arrives.
Deleting rows because one field is missing
The row usually contains other data you need. Removing a customer for a missing email removes their revenue too.

Fix: Flag and keep. Remove only when the row is genuinely unusable without that field.
Replacing missing numbers with zero
A missing revenue and a genuine zero revenue are different facts, and after replacement nothing distinguishes them. Averages become wrong.

Fix: Leave it null. DAX aggregations ignore blanks correctly. If a default is genuinely right — a null discount meaning no discount — replace it, and say so in the cleaning log.
Confusing empty string with null
A filter for <> null keeps empty strings, so "rows with an email" quietly includes rows with no email.

Fix: Normalise empty and whitespace-only values to genuine null early, so there is one kind of nothing.
Building a mapping with no unmapped-value check
The mapping covers today's data perfectly. A new value appears and silently produces a null or an unexpected category, and nobody finds out.

Fix: Always check for nulls after a mapping merge, and load a check query that surfaces them.
10

Professional Tip

Ship a data-quality check query with every model
Reference your cleaned queries, filter each to the rows that should not exist, combine them with an Issue column, and load the result. Then put a card on a hidden validation page showing its row count.

Typical checks worth including: unmapped values after any lookup · duplicate keys · rows failing a type conversion · dates outside the expected range · negative values in a column that should never be negative · a total that should reconcile against another source.

This takes twenty minutes to build and it converts silent data drift into a visible number. It is the closest thing Power BI has to a unit test, and almost nobody does it — which is exactly why doing it marks you out.

One more:

  • Keep the raw value alongside the standardised one while developing. Two columns — Country Raw and Country — make it trivial to see what mapped to what. Remove the raw column only once you are confident.
11

Knowledge Check

Knowledge Check

5 questions. Answer each one to see why the right answer is right.

A filter each [Email] <> null is meant to keep only customers with an email. Why might it not work?

You have 25 spellings of 6 countries. What is the approach that scales?

A customer has no email address. What is usually the best treatment?

Why check for nulls in the standardised column after a mapping merge?

Before building a mapping table for a column with inconsistent casing, what should you try first?

12

Practical Challenge

Practical Challenge

Build a self-monitoring cleaning pipeline

Complete the standardisation of bwb-messy-customers.csv and add a data-quality check query that would tell you if the source changed. Then prove the check works by deliberately breaking something.

Requirements
  • Country standardised from 25 values to 6, via a mapping table
  • Status standardised from 7 values to 3
  • Empty strings normalised to genuine nulls
  • A Has Email flag rather than deleted rows
  • A check query covering at least three distinct failure conditions
  • Evidence that the check fires when you break something, and returns zero rows when you fix it
  • A short note on what the check does not cover

For the check query, think about what "wrong" means for this specific dataset. Unmapped values are one class. What else could change at source that would matter?

  • A CustomerID appearing more than once.
  • A revenue value that will not convert to a number.
  • A signup date in the future, or before the business existed.
  • A row count that drops sharply between refreshes.

For the final requirement, be honest. A check that catches everything does not exist, and pretending otherwise is worse than knowing the gaps.

13

Solution

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

The pipeline

Power Query M · Customers Cleaned — the standardisation query
let
    Reduced = #"Customers Reduced",   // output of Lesson 2

    // ---- normalise the nothings ----------------------------------
    #"Empties to null" =
        Table.TransformColumns(Reduced, {
            {"Email",   each if Text.Trim(_ ?? "") = "" then null else Text.Trim(_), type text},
            {"Country", each if Text.Trim(_ ?? "") = "" then null else Text.Trim(_), type text},
            {"Status",  each if Text.Trim(_ ?? "") = "" then null else Text.Trim(_), type text}
        }),

    // ---- free transformations first -------------------------------
    #"Lowercase status" =
        Table.TransformColumns(#"Empties to null",
            {{"Status", each Text.Proper(Text.Lower(_ ?? "")), type text}}),

    // ---- join key for the country mapping -------------------------
    #"Add country key" =
        Table.AddColumn(#"Lowercase status", "CountryKey",
            each Text.Lower(Text.Trim([Country] ?? "")), type text),

    #"Merge country mapping" =
        Table.NestedJoin(#"Add country key", {"CountryKey"},
            CountryMapping, {"RawKey"}, "map", JoinKind.LeftOuter),

    #"Expand standard country" =
        Table.ExpandTableColumn(#"Merge country mapping", "map",
            {"Standard"}, {"Country Standard"}),

    // ---- strip revenue formatting BEFORE typing --------------------
    #"Strip currency symbols" =
        Table.TransformColumns(#"Expand standard country",
            {{"LifetimeRevenue", each
                if _ = null then null
                else Text.Remove(Text.Trim(_), {"$", ",", " "}), type text}}),

    // ---- flag rather than delete -----------------------------------
    #"Add Has Email flag" =
        Table.AddColumn(#"Strip currency symbols", "Has Email",
            each [Email] <> null, type logical),

    #"Remove helper columns" =
        Table.RemoveColumns(#"Add Has Email flag", {"CountryKey", "Country"}),

    #"Rename standard country" =
        Table.RenameColumns(#"Remove helper columns", {{"Country Standard", "Country"}})
in
    #"Rename standard country"

The check query

Power Query M · DQ Checks — returns rows only when something is wrong
let
    Src = #"Customers Cleaned",

    // 1. a country arrived that the mapping does not cover
    Unmapped =
        Table.AddColumn(
            Table.SelectRows(Src, each [Country] = null),
            "Issue", each "Unmapped country", type text),

    // 2. CustomerID is meant to be unique
    DupKeys =
        Table.AddColumn(
            Table.SelectRows(
                Table.Group(Src, {"CustomerID"}, {{"n", Table.RowCount, Int64.Type}}),
                each [n] > 1),
            "Issue", each "Duplicate CustomerID", type text),

    // 3. revenue that will not convert to a number
    BadRevenue =
        Table.AddColumn(
            Table.SelectRows(Src, each
                [LifetimeRevenue] <> null
                and (try Number.From([LifetimeRevenue]))[HasError]),
            "Issue", each "Revenue will not convert", type text),

    // 4. a signup date outside the plausible range
    BadDate =
        Table.AddColumn(
            Table.SelectRows(Src, each
                let d = (try Date.From([SignupDate]))[Value]? in
                d <> null and (d < #date(2020,1,1) or d > Date.From(DateTime.LocalNow()))),
            "Issue", each "Signup date out of range", type text),

    Combined = Table.Combine({
        Table.SelectColumns(Unmapped,   {"CustomerID", "Issue"}, MissingField.UseNull),
        Table.SelectColumns(DupKeys,    {"CustomerID", "Issue"}, MissingField.UseNull),
        Table.SelectColumns(BadRevenue, {"CustomerID", "Issue"}, MissingField.UseNull),
        Table.SelectColumns(BadDate,    {"CustomerID", "Issue"}, MissingField.UseNull)
    })
in
    Combined

Proving it fires

TestActionExpectedResult
BaselineRefresh with the mapping complete0 rows✓ 0 rows
Unmapped countryDelete the PAK row from the mappingRows with Pakistan appear, Issue = Unmapped country✓ fires
Duplicate keyDuplicate one row in the source fileThat CustomerID appears, Issue = Duplicate CustomerID✓ fires
Bad revenueChange one revenue value to N/AThat row appears, Issue = Revenue will not convert✓ fires
RestoreUndo all three0 rows✓ 0 rows

What the check does not cover

Being honest about the gaps
1. Values that are wrong but plausible. If a revenue figure arrives as 34210.64 when it should be 3421.06, nothing here catches it. The value is well-formed, in range, and converts cleanly. Only a reconciliation against another source would find it.

2. A new spelling that happens to match an existing mapping entry incorrectly. If GE arrived meaning Georgia and my mapping has ge → Germany, it maps silently and wrongly. The check confirms a value mapped, not that it mapped correctly.

3. Rows that stop arriving. If the export starts omitting one country entirely, every remaining row is valid and the check stays at zero. A row-count trend check would be needed — comparing this refresh's count against the last few.

4. Semantic changes. If Status = 'Active' starts meaning something different in the source system, no structural check can detect that. Only a conversation can.

Why the honesty section is the best part of this answer

A check query that its author believes catches everything is more dangerous than no check at all, because it creates false confidence. The four gaps above are real and they are not fixable by adding more conditions — three of them require information that is not in the file.

Stating them explicitly does two things: it tells whoever inherits the model what they still need to watch, and it demonstrates that you understand the difference between structural validity — which you can automate — and semantic correctness, which you cannot. That distinction is the whole lesson.

14

Lesson Summary

Key takeaways
  • null, empty string and whitespace are three different things. Normalise them to genuine null early so there is one kind of nothing.
  • Try the free transformations first — Trim, Clean, lowercase. They collapsed 7 status spellings to 3 with no mapping at all.
  • Standardise at scale with a mapping table and a left outer merge, not with repeated Replace Values steps.
  • Always check the standardised column for nulls. A null means an unmapped source value — the change you want to know about.
  • For missing values, default to flagging. Removing loses data; replacing invents it; flagging preserves both facts.
  • Clean the content before setting the type. Strip currency symbols and separators, then convert.
  • Ship a data-quality check query that returns rows only when something is wrong, and surface its count on a validation page.
  • Know what your checks do not cover. Structural validity can be automated; semantic correctness cannot.

Syllabus topics delivered

Replacing valuesHandling null valuesData-quality checksnull vs blank vs empty string
15

Progress

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

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