3 Data-Cleaning Questions With No Universal Answer
Should you delete duplicates, fill missing values, or use Power Query? Three common data-cleaning questions explained with practical examples and a safer investigation-first approach.
Remove duplicates. Fill the blanks. Standardize everything.
That sounds like data cleaning.
Sometimes it is.
Sometimes it is how good data gets destroyed.
People keep asking versions of the same questions around Excel, Power Query, Python, duplicates, missing values, and messy operational files. The questions sound technical. Often they are questions about meaning.
A tool can execute an instruction perfectly and still produce the wrong result.
Excel can delete exactly the rows you told it to delete. Power Query can transform exactly the values you instructed it to transform. Python can automate the same operation across millions of rows. None of those tools can rescue a bad assumption.
This article was inspired by recurring questions from Excel and data-analysis communities on Reddit. The threads change. The pattern does not: people ask how to clean data before they have decided what “clean” should mean.
Three questions show up again and again:
- What is the best tool for cleaning messy data?
- Should duplicate records always be removed?
- What should you do with missing values?
None of them has a universal answer. That is the point.
What is the best tool for cleaning messy data?
There is no universally best tool, because “cleaning data” is not one operation.
It is several different jobs wearing the same label.
If you already know the comparison between Excel, Power Query, OpenRefine, CRM tools, and investigation software, see Excel, Power Query, OpenRefine, your CRM, or Auditere?. The shorter version for this question is job-first, tool-second.
Case A — A small spreadsheet with obvious manual fixes
Example: about 300 rows. A few names have inconsistent capitalization. Some dates need correcting. Two obvious typos.
Excel may be entirely sufficient.
Do not over-engineer a tiny problem. If you can see the issue, understand it, and fix it safely by hand, that is not a failure of sophistication. It is good judgment.
Case B — A repeatable transformation workflow
Example: every week you receive the same export. You repeatedly:
- rename headers
- split columns
- normalize dates
- combine source files
- filter records
- reshape the table
This is where Power Query earns its keep.
The important word is repeatable.
Once the transformation is known, automation becomes valuable. You are no longer discovering what the data means. You are executing a rule you already trust.
Case C — Large or highly programmable datasets
Example: hundreds of thousands or millions of records. Complex rules. Version-controlled logic. Operations that become cumbersome inside a spreadsheet interface.
Python or SQL may become the better environment — not because spreadsheets are “amateur,” but because the scale and control requirements have changed.
Case D — You do not yet know which records are actually wrong
Examples:
- two customer records look similar but not identical
- one order ID appears several times
- a value is unusual but still plausible
- two fields disagree
- a column contains blanks whose meaning is unclear
This is not primarily a transformation problem.
It is an investigation problem.
Before deciding how to change the data, first establish whether anything should be changed at all. That is closer to finding bad data in Excel than to running Remove Duplicates on autopilot.
Better question
Instead of:
“What is the best data-cleaning tool?”
Ask:
What decision am I trying to make about this data?
If the decision is already known and you simply need to execute a transformation, use the most efficient transformation tool.
If the decision itself is uncertain, investigate first.
Should duplicate records always be removed?
No.
A repeated value is not automatically a duplicate record.
Same ID, different realities
| Order ID | Status | Date |
|---|---|---|
| 10422 | Shipped | August 1 |
| 10422 | Delivered | August 3 |
Same Order ID.
If the dataset records order history, both rows may be legitimate. The second row is not a “mistake” waiting to be deleted. It may be the later state of the same order.
Repeated identifiers can represent:
- transaction history
- multiple line items
- state changes
- repeated events
- one-to-many relationships
Exact copies look easier — and still deserve caution
| Customer | Phone | |
|---|---|---|
| Sarah Khan | [email protected] | 555-0142 |
| Sarah Khan | [email protected] | 555-0142 |
This looks much more like a true duplicate.
Even here, deletion may not be the first action.
Ask:
- Do both records have related transactions?
- Did two systems create separate records?
- Does one contain newer information?
- Does one contain notes or metadata the other does not?
- Could deleting one break a relationship elsewhere?
- Is there a source-of-truth issue?
Detection tells you where to look. Deletion changes what the organization treats as real.
Three different classes of “duplication”
1. Exact duplicates
Every relevant field is identical. Usually the easiest case. Still useful to understand why duplication occurred — import loop, copy-paste, system sync — so it does not keep returning.
2. Duplicate entities
Two records may represent the same real-world person, company, supplier, or product, but the values differ.
| Name | Company | |
|---|---|---|
| James Lee | Northstar Ltd | [email protected] |
| James Lee | Northstar Limited | [email protected] |
These may be one person.
Or they may not.
Similarity is not identity. Near-matches need review, not blind merge. The same tension shows up in CRM CSV cleanup and contact verification before import.
3. Repeated identifiers
An ID appears multiple times. That could be a problem. It could also be legitimate business history.
The rule worth keeping
Detect duplicates aggressively. Delete duplicates conservatively.
Detection is cheap.
Deletion changes reality.
Those are not the same operation.
Why “Remove Duplicates” can keep the wrong row
Imagine a property listing history:
| Listing ID | Status | Updated |
|---|---|---|
| L-881 | Active | March 2 |
| L-881 | Pending | March 10 |
| L-881 | Sold | March 18 |
If someone sorts poorly, or runs a naive remove-duplicates on Listing ID alone, Excel or Power Query may keep whichever row it considers “first.”
Keep Active. Drop Sold.
The file now looks cleaner.
It is also wrong.
The tool did exactly what it was asked. The assumption was the failure.
What should you do with missing values?
Reject universal answers such as:
- fill with zero
- fill with average
- fill with median
- forward-fill
- delete the row
- write N/A
Every one can be correct in some context.
Every one can also be wrong.
The first question should be:
Why is the value missing?
Case 1 — The value does not apply
Example: Cancellation Date.
A customer who never cancelled has a blank.
The blank may be correct.
Filling it would manufacture information.
Case 2 — The field was never collected historically
Example: the company introduced a new field in 2025. Records created in 2023 naturally have blanks.
Those blanks may describe business history rather than bad data. Treating them as “errors” rewrites the past.
Case 3 — Something failed
Possible causes:
- integration failure
- import failure
- human omission
- source system stopped sending the field
- broken mapping
Now the blank may be evidence of an operational problem. The useful action is investigation — not cosmetic fill.
Case 4 — The value is genuinely unknown
Sometimes the correct answer is:
Unknown.
Unknown should be treated as a valid state when evidence is insufficient.
One blank, many meanings
A sales file contains a blank Discount % field.
Possible meanings:
- no discount
- discount unknown
- field not applicable
- data missing
- old system never tracked discounts
These are completely different realities represented by the same empty cell.
If you automatically replace every blank with 0, you have made a business claim:
“No discount was given.”
But the data never proved that.
Do not manufacture information merely because an empty cell is inconvenient.
For operational files heading into CRM or finance systems, blanks deserve the same caution as pre-import CSV checks and missing or inconsistent field review.
The real problem isn't dirty data. It's premature certainty.
The three questions look unrelated:
- which tool should I use?
- should I remove duplicates?
- what should I do with missing values?
Underneath them is the same error:
Taking action before establishing meaning.
Deleting repetition assumes repetition is wrong.
Filling a blank assumes a value should exist.
Standardizing variation assumes the variation is accidental.
Changing a category assumes one representation is correct.
Automating a transformation assumes the transformation itself is justified.
The difficult part is often not knowing how to change a cell.
It is determining whether the cell should be changed at all.
That is the bridge from data cleaning to data investigation — the same bridge described in audit before you import.
Clean-looking data is not necessarily correct data.
A safer sequence: investigate, then transform
1. Preserve the original
Never perform destructive cleanup on the only copy.
2. Understand what one row represents
Order? Customer? Event? Transaction? Product? Account? Shipment?
Without this, even duplicate detection can be misleading.
3. Establish what important fields mean
Do not rely purely on column names.
Status could mean payment status, delivery status, account status, order status, or pipeline stage. Same word. Different jobs.
4. Detect questionable records
Look for duplicates, missing values, inconsistent formats, conflicting fields, unusual values, and unexpected relationships.
5. Separate obvious errors from ambiguity
A malformed date may be a deterministic format error.
A rare but valid date may only be unusual.
Do not treat those the same. Flagging both for review is useful. Treating both as automatic cleanup candidates is not.
6. Resolve only what the evidence supports
Correct what is clearly wrong.
Preserve what is legitimate.
Flag what remains uncertain.
7. Automate only after the rule is understood
Then Excel, Power Query, SQL, Python, or another tool can execute safely.
Question → Meaning → Evidence → Decision → Transformation
Skip the middle of that chain and the last step becomes a confident way to damage the file.
Where Auditere fits — and where it does not
If you have a small file and already understand exactly what needs changing, a spreadsheet may be enough.
If you repeatedly perform a known transformation, Power Query may be the better answer.
If you are processing very large datasets or programmable pipelines, Python or SQL may make more sense.
Auditere is designed for the stage before blind transformation becomes safe:
- finding questionable records
- showing why they were flagged
- helping the operator review them
- resolving issues deliberately
- verifying the resulting data
Auditere is designed to help answer the harder question: what in this dataset actually deserves changing?
Sometimes the most important thing you can do to a suspicious value is leave it alone until you understand it.
Related reads: Why "fixing" a spreadsheet can make it less trustworthy · Excel vs Power Query vs OpenRefine vs CRM vs Auditere · Finding bad data in Excel · How to clean a CSV before CRM import · Verify contact data before CRM import · Audit before you import · Find duplicate records · Investigate missing and inconsistent data
Auditere is live for beta. Start your free trial or view pricing — only if investigation, not transformation, is the bottleneck.