Dataset Profiling: Missing Values and Duplicate Records

Welcome back. In the previous lesson, you created a data dictionary: a statement of what each field should mean, which values are valid, and what blanks represent. Now you will compare that documented expectation with the actual extract.

A data profile is a concise audit of a dataset’s structure and quality. For this lesson, the focus is deliberately narrow: quantify missing values and duplicate records before attempting to clean anything. By the end, you should be able to produce an evidence-based profile that states where data is missing, how much is missing, what definition of duplicate you used, and which records need investigation.

---

Profile before you clean

It is tempting to fix blanks or click “Remove Duplicates” as soon as you see a problem. Resist that impulse initially. Cleaning without measurement makes it impossible to explain:

• what was wrong in the original extract;
• how widespread the issue was;
• whether a cleaning step changed a KPI;
• whether the same problem returns in the next refresh.

Profiling is therefore an audit step, not a cleanup step.

For a service-desk dataset, missing values and duplicates can directly distort business results:

• A missing Resolved_Date can exclude a ticket from a monthly resolution count.
• A missing SLA_Breached result can make SLA performance look better or worse depending on how it is handled.
• A duplicated ticket can inflate ticket volume, resolution counts, or breach counts.
• A duplicated Ticket_ID with different values may signal a genuine update, a faulty export, or an unclear dataset grain.

The data dictionary from the previous lesson is your benchmark. It tells you, for example, that Ticket_ID should be nonblank and unique, while Assignment_Group_At_Resolution may require review when blank. Profiling tells you whether those rules hold in this extract.

---

What exactly should you count?

A useful profile uses clear definitions and denominators. Do not write “there are some blanks” or “duplicates were found.” State the count, the rate, and the rule used.

Missing values: count both cells and affected records

For each column, calculate:

• Total records, $N$: the number of rows in the dataset, excluding the header.
• Missing-cell count, $M_j$: the number of blank cells in field $j$.
• Missing rate: the share of records that are blank in field $j$.

$$\text{Missing rate for field }j=\frac{M_j}{N}\times 100\text{ percent}$$

For example, suppose a dataset contains 2,000 ticket records and 65 have a blank assignment group:

$$\frac{65}{2000}\times 100\text{ percent}=3.25\text{ percent}$$

Your profile should report this as:

Assignment_Group_At_Resolution: 65 missing values out of 2,000 records, or 3.25 percent.

Also measure rows affected by missing data, especially for fields required by your analysis. This is not simply the sum of missing values across columns, because the same ticket can have several missing fields.

For example:

• 18 tickets have no Resolved_Date.
• 65 tickets have no assignment group.
• 73 tickets have at least one missing required reporting field.

The value of 73 is the operational impact: 73 records may need review before you produce a monthly SLA report.

Blank is not the only form of missingness

COUNTBLANK detects empty cells, but datasets often encode missingness with text values:

• Unknown
• N/A
• Not Applicable
• -
• TBD
• an empty-looking text value created by a formula

These are not interchangeable. Your prior data dictionary should tell you whether they are valid analytical categories, missing values, or invalid entries.

For example:

Observed value in SLA_Breached	Likely interpretation	Profile separately?
Blank	No value supplied	Yes
Unknown	SLA applies, but outcome is undetermined	Yes
Not applicable	Ticket is not eligible for the SLA	Yes
No	Valid measured outcome	No
0	Could be a valid numeric value in another field	Never assume it is missing

A missing-value profile should therefore distinguish blank values from coded missing values. Do not silently convert either to zero, No, or a favorable KPI result.

---

Quantifying missing values in Excel

Excel is enough to create a solid first profile for a static CSV extract. Work on a copy of the file or a separate worksheet named Profile; preserve the original data unchanged.

A practical profiling sheet

Assume your raw data has headers in row 1 and 2,000 ticket records in rows 2 through 2001. Create a small table on a Profile worksheet:

Field	Total records	Blank count	Blank rate	Dictionary rule	Initial status
Ticket_ID	2,000	4	0.20%	Required and unique	Investigate
Resolved_Date	2,000	18	0.90%	Required for resolved-ticket reporting	Investigate
Assignment_Group_At_Resolution	2,000	65	3.25%	Approved group name required	Investigate
SLA_Breached	2,000	9	0.45%	Blank not permitted if SLA applies	Check eligibility rule

Use formulas such as:

• Record count: =ROWS(Raw_Data!A2:A2001)
• Blank count for Resolved_Date: =COUNTBLANK(Raw_Data!C2:C2001)
• Blank rate: =C2/B2

Format the blank-rate column as a percentage with one or two decimal places.

Inspect the affected rows

A count tells you how many values are missing, but not why. For any material issue:

1. Filter the relevant column for Blanks.
2. Examine related fields in those records.
3. Compare what you see with the data dictionary’s rules.
4. Record a factual observation before proposing a fix.

Suppose all blank SLA_Breached values occur where SLA_Applicable = No. That may be a valid design choice—but it conflicts with a dictionary that expects Not applicable, not blank. Your profile should state that inconsistency clearly.

If blank Resolved_Date values appear in a table whose stated grain is “one row per resolved incident,” that is more serious: either the dataset scope is not what its name claims, or the source data has incomplete records.

---

Duplicates are defined by the dataset’s grain

A repeated value is not automatically a duplicate record.

For instance, several tickets can legitimately share the same:

• assignment group;
• priority;
• resolution date;
• customer organization;
• product category.

Those fields are dimensions, not unique identifiers. The duplicate rule must follow the dataset grain and its documented key.

If the grain is one row per incident ticket, then Ticket_ID should normally be unique. Multiple rows with the same Ticket_ID are duplicate-key candidates.

However, they may not be exact copies. They could represent:

• a ticket that was exported twice by mistake;
• two versions of a ticket at different update times;
• separate event-history records incorrectly mixed into a ticket-level extract;
• a valid business situation that contradicts your assumed grain.

This distinction matters:

Check	Rule	What it detects
Exact-row duplicate	Every selected field is identical	Repeated copies of the same full record
Duplicate key	A documented key, such as Ticket_ID, appears more than once	Multiple records claiming to represent the same entity
Business duplicate	A business-defined combination repeats, such as customer + order date + item	Possible duplicate when no single identifier is reliable

A dataset can have duplicate keys without having exact duplicate rows. That is often the more important issue, because inconsistent versions of the same ticket can affect your analysis in different ways.

Quantify duplicate records with two numbers

Report both:

1. Duplicate groups: how many distinct keys appear more than once.
2. Excess records: how many records remain after retaining one record per duplicated key.

For example:

Ten Ticket_ID values occur more than once. Together, they create 12 excess records beyond the first occurrence of each ID.

If one key appears three times, it represents:

• one duplicate group;
• two excess records.

This is more informative than simply saying “three duplicates.”

A safe Excel workflow

Use a copy of the data for the counting process. Do not use “Remove Duplicates” on your source sheet before you have reviewed the records and documented the result.

1. Check the key rule first. Filter Ticket_ID for blanks and profile those separately. Blank IDs cannot reliably participate in a uniqueness check.
2. Inspect duplicate keys. Sort the data by Ticket_ID, then look for repeated IDs. Compare all columns for the repeated records.
3. Measure exact duplicate rows. On a temporary copy, select the full dataset and use Data → Remove Duplicates with every column selected. Excel reports how many duplicate values it found and removed. Record that number, then discard the temporary copy if needed.
4. Measure duplicate keys. On another temporary copy, use Data → Remove Duplicates with only Ticket_ID selected. The number Excel removes is the count of excess key occurrences.
5. Return to the original dataset to investigate. Determine whether the duplicates are identical copies, conflicting versions, or valid repeated events caused by an incorrect grain assumption.

At the profiling stage, your decision may simply be:

Do not remove duplicate-key candidates until the source-system owner confirms whether the dataset should contain one current record or an event history per ticket.

That is a sound analytical conclusion.

---

Turn counts into a short quality profile

A profile should be compact enough that a stakeholder or another analyst can review it quickly. Here is an illustrative profile for a resolved-incidents export.

Check	Rule used	Result	Interpretation / next action
Records	Rows 2–2001	2,000 records	Baseline denominator for field-level rates
Blank Ticket_ID	Must be nonblank	4 records, 0.20%	Cannot be reliably deduplicated; investigate source export
Blank Resolved_Date	Required in resolved-ticket extract	18 records, 0.90%	Exclude only under a documented reporting rule; verify dataset scope
Blank assignment group	Required to report by team	65 records, 3.25%	Review assignment-history logic or source completeness
Rows with any missing required field	Ticket_ID, Resolved_Date, or assignment group blank	73 records, 3.65%	Assess impact on planned KPI
Exact duplicate rows	All columns match an earlier row	7 excess records	Candidate export duplication; retain evidence
Duplicate Ticket_ID values	Ticket_ID expected to be unique	12 excess records across 10 IDs	Compare versions; confirm correct table grain

Notice the wording. The profile does not claim that all 12 duplicate-key records should be deleted. It reports what was observed, the rule used, and the next investigation.

Prioritize issues by analytical risk

Not every issue has the same impact. Prioritize using four questions:

1. Is the field required for the stakeholder’s question or KPI?
2. Does the issue violate a documented rule?
3. How frequent is it?
4. Is it concentrated in a specific team, month, priority, or source?

For example, 65 missing assignment groups may matter more to a report grouped by team than 500 missing optional customer comments. Conversely, four blank ticket IDs may be low in percentage terms but high in risk because identifiers support joins, distinct counts, and duplicate checks.

---

A Power Query preview of the same discipline

You will use Power Query in depth later in the course, but it is helpful to see how its built-in profiling features reflect the same questions you have just asked in Excel.

The key principle is not tool-specific: a metric is only as complete as the data it evaluates. When your dataset is small enough for a full profile, profile every row. When it is too large, state clearly that the result is based on a sample and use a more appropriate full-data check before reporting final quality figures.

---

A repeatable 15-minute profiling routine

For each new static dataset, use this sequence:

1. Confirm scope and grain. State what one row represents.
2. Identify the expected key. Use the data dictionary or source documentation.
3. Count total records. Write down the denominator.
4. Profile blanks per field. Prioritize fields required for your planned analysis.
5. Check coded missing values. Filter for values such as Unknown, N/A, and Not applicable.
6. Count exact duplicate rows.
7. Count duplicate keys. Keep this separate from exact duplicates.
8. Inspect representative affected records. Counts alone do not establish cause.
9. Document results, rule, impact, and next action.
10. Keep the raw extract unchanged. Make cleaning decisions only after the profile is recorded.

This habit will carry into Excel, SQL, pandas, and Power BI. The syntax will change, but the audit questions remain the same.

---

Key takeaways

A data profile converts vague concerns about “messy data” into measurable evidence.

• Calculate a missing count and missing rate for each important field, using the total record count as the denominator.
• Also count records affected by missing required fields; do not add column counts because the same row may have several blanks.
• Treat blank cells, Unknown, Not applicable, and zero as distinct states unless the data dictionary explicitly defines them otherwise.
• Define duplicates using the dataset’s grain and key, not merely by looking for repeated category values.
• Report both exact duplicate rows and duplicate-key candidates. They diagnose different problems.
• Profile first, inspect the affected records, document the findings, and only then decide whether any cleaning is appropriate.

Next, you will build an analysis plan that maps a stakeholder question to the fields, calculations, quality checks, and outputs required to answer it. The profile you created here will help you judge whether the available data is fit for that plan.