Replacing Values, Nulls & Quality Checks
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.
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.
- 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
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.
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.
Concept
Null, blank and empty string
These look the same in the preview and behave differently. Getting them straight prevents a lot of confusion.
| What it is | In Power Query | In DAX | |
|---|---|---|---|
| null | No value at all — the absence of data | Shown as null in italics | BLANK() |
| empty string | A text value of zero length | Shown as an empty cell, but it is a value | An empty string, not blank |
| whitespace | A string containing only spaces | Looks empty; is not | A non-empty string |
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.
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
| Option | When it is right | When it is wrong |
|---|---|---|
| Remove the row | The row is unusable without that field — a sales row with no amount | The field is incidental. Removing a customer because their email is missing loses their revenue too |
| Replace with a value | There is a genuine, defensible default — Discount null means 0 | You are inventing data. Replacing a missing revenue with 0 understates the total and looks like a real zero |
| Flag and keep | Usually. Keep the row, add a flag column, let the report decide | Rarely wrong. Slightly more work |
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
- Create a two-column table:
RawandStandard. - Merge it onto your data on the raw value, using a left outer join.
- Expand the
Standardcolumn. - Check for nulls in Standard — those are values not in your mapping.
- 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
| Option | Good | Less good |
|---|---|---|
| Enter Data in Power Query | Self-contained; travels with the file | Editing it means opening Power Query |
| A CSV or Excel file | A non-technical colleague can maintain it | Another file to keep track of |
| A database table | Shared across every report that needs it | Needs 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:
| Transformation | Effect | Where |
|---|---|---|
| Trim | Removes leading and trailing whitespace | Transform › Format › Trim |
| Clean | Removes non-printable characters | Transform › Format › Clean |
| lowercase | Case-folds — turns Active/active/ACTIVE into one value | Transform › Format › lowercase |
| Capitalize Each Word | Proper-cases names | Transform › Format › Capitalize Each Word |
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.
// 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
IssuesLoad 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.
Visual Explanation
Step through the standardisation on real data.
What the mapping table looks like
| Raw | Standard |
|---|---|
| us | United States |
| USA | United States |
| U.S.A. | United States |
| United States of America | United States |
| UK | United Kingdom |
| U.K. | United Kingdom |
| england | United Kingdom |
| Great Britain | United Kingdom |
| AUS | Australia |
| australia | Australia |
| CA | Canada |
| canada | Canada |
| DE | Germany |
| deutschland | Germany |
| PAK | Pakistan |
| pakistan | Pakistan |
UK, uk and Uk all match one mapping row, and your table stays a fraction of the size it would otherwise need to be.Power BI Demonstration
null in Replace with. Tick Advanced options › Match entire cell contents.Raw and Standard. Enter the pairs. Name the query Country Mapping.Text.Lower(Text.Trim([Country])). Do the same on the mapping table's Raw column.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.Country.$, replace with nothing. Repeat for ,. Now the column will type cleanly.DQ - Unmapped Values. Filter it to rows where the standardised country is null. Load it.A check you have never seen fire is a check you do not know works.
Example Dataset
Continuing with the same file. If you have Lesson 2's query, build on it.
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 |
|---|---|
| Country distinct, before standardising | 25 |
| Country distinct, after mapping | 6 |
| Status distinct, after lowercase alone | 3 — no mapping table needed |
| Nulls in Country Standard | 0, if the mapping is complete |
| Check query row count on clean data | 0 |
| Check query after removing a mapping entry | The rows carrying that raw value |
The three null strategies compared
| Strategy | Customers | Total revenue | Problem |
|---|---|---|---|
| Remove rows with no email | 28 fewer | Understated | You deleted real customers and their real revenue to fix a contactability problem |
| Replace email with a placeholder | Unchanged | Correct | Invented data. "unknown@example.com" looks like an address and will eventually be emailed |
| Flag and keep | Unchanged | Correct | None. Both facts preserved; the report decides |
This is the general argument for flagging: it keeps a local problem local.
Common Mistakes
Fix: One mapping table plus a merge, plus a null check on the result. Scales, and it tells you when something new arrives.
Fix: Flag and keep. Remove only when the row is genuinely unusable without that field.
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.
<> 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.
Fix: Always check for nulls after a mapping merge, and load a check query that surfaces them.
Professional Tip
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 RawandCountry— make it trivial to see what mapped to what. Remove the raw column only once you are confident.
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?
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.
- 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.
Solution
Try the challenge properly before opening this. Reading a solution you have not attempted feels like learning, but it is not.
The pipeline
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
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
CombinedProving it fires
| Test | Action | Expected | Result |
|---|---|---|---|
| Baseline | Refresh with the mapping complete | 0 rows | ✓ 0 rows |
| Unmapped country | Delete the PAK row from the mapping | Rows with Pakistan appear, Issue = Unmapped country | ✓ fires |
| Duplicate key | Duplicate one row in the source file | That CustomerID appears, Issue = Duplicate CustomerID | ✓ fires |
| Bad revenue | Change one revenue value to N/A | That row appears, Issue = Revenue will not convert | ✓ fires |
| Restore | Undo all three | 0 rows | ✓ 0 rows |
What the check does not cover
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.
Lesson Summary
- 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
Progress
Marking a lesson complete stores your progress in this browser and updates your BitWithBite learner dashboard. Nothing is uploaded anywhere.