Data Profiling and Quality Assessment

Introduction

Business intelligence is only as reliable as the data on which it is based. A Power BI report may contain attractive visualisations and sophisticated measures, yet still produce misleading conclusions if customer names are inconsistent, dates are invalid, sales amounts are duplicated, or missing values are ignored. Data profiling is the systematic examination of data structure, content, and quality before the data is used for analysis.

In Power BI, profiling is performed primarily in Power Query. Power Query provides column-quality indicators, value-distribution summaries, column profiles, filters, data-type detection, and transformation functions. These features help an analyst identify problems early, document their significance, and decide whether to correct, exclude, retain, or escalate questionable records.

Quality assessment is not simply a search for blanks. It is an investigation of whether data is suitable for a defined business purpose. A missing discount rate may be harmless if no discount was offered, but it may be a serious defect when calculating net revenue. Similarly, a duplicated transaction may be a genuine repeated purchase or an accidental duplicate import. Context and business rules are therefore essential.

Learning Objectives

By the end of this chapter, you should be able to:

• explain the purpose and scope of data profiling;
• use Power Query column quality, distribution, and profiling features;
• distinguish nulls, errors, duplicates, outliers, and invalid values;
• apply filters and descriptive statistics to investigate data;
• recognise inconsistent representations of the same business entity;
• select appropriate cleansing actions without destroying useful information;
• document quality findings and communicate their business implications; and
• apply a structured quality-assessment workflow to a practical dataset.

1. Data Quality as Fitness for Use

Data quality is multidimensional. A dataset is not simply “good” or “bad”; it can be fit for one purpose and unsuitable for another. Common dimensions include:

Power Query can reveal evidence about completeness, validity, consistency, and uniqueness. It cannot, by itself, prove real-world accuracy. Accuracy often requires comparison with an authoritative source, such as an invoice system or approved master-data table.

A useful distinction is between a data defect and an analytical exception. A null delivery date for an order that has not shipped may be valid business information. A null delivery date for an order marked “Delivered” is likely a defect or reconciliation issue.

2. The Power Query Profiling Environment

When a table is opened in Power Query Editor, the analyst can enable profiling features from the View tab. The principal tools are:

Column quality

Column-quality information commonly identifies the proportion of values that are valid, empty, or errors. In Power Query, “empty” generally refers to null values. A value can be non-null but still invalid according to business rules; therefore, a high valid percentage does not guarantee high quality.

For example, a column containing the text value Unknown in every row may show no nulls, although the business meaning is incomplete. Likewise, a negative quantity may be syntactically valid as a number but invalid for a sales-order rule.

Column distribution

Column distribution displays distinct values and their frequencies. It is useful for finding:

• unexpected category spellings, such as UK, U.K., and United Kingdom;
• rare values that may represent typing errors;
• excessive concentration in one category;
• suspiciously repeated identifiers; and
• unexpected values outside an approved list.

A distinct value is a different value observed in the column. A unique value is a value that occurs only once. The two concepts should not be confused. If a customer-status column contains three distinct statuses and one occurs once, there are three distinct values but only one unique occurrence.

Column profile

Column profile provides more detailed information, often based on the first 1,000 rows by default or on the entire dataset when the analyst changes the profiling scope. This setting is critical. A defect occurring after row 1,000 may be invisible when only the preview is profiled.

Before drawing conclusions, confirm:

1. whether the profile covers the entire dataset;
2. the number of rows being assessed;
3. the detected data type;
4. whether errors have already been removed; and
5. whether filters or earlier transformations have changed the population.

A profile is always conditional on the current query step. Profiling before filtering and profiling after filtering answer different questions.

3. A Structured Profiling Workflow

A disciplined workflow reduces the risk of making premature corrections.

Step 1: Establish the business purpose

Define what the table represents, the grain of each row, required columns, permitted values, and key relationships. For example, one row may represent one invoice line rather than one invoice. If the grain is misunderstood, apparent duplicates may be legitimate.

Step 2: Preserve the source and create a profiling branch

Do not overwrite the raw source. In Power Query, retain an unchanged staging query and create a reference or duplicate for profiling. This preserves traceability and allows transformations to be reviewed.

Step 3: Inspect structure and types

Review column names, data types, row counts, and representative values. Automatic type detection is useful but must be checked. A product code such as 001245 should usually be text, not a whole number, because converting it to a number removes leading zeros.

Step 4: Profile each column

Use quality, distribution, and profile information. Record the observed issue, affected rows, likely cause, business impact, and proposed action.

Step 5: Test business rules

Examples include:

• OrderDate must not be later than the reporting date.
• Quantity must be greater than zero for a completed sale.
• UnitPrice must not be negative.
• Status must belong to an approved set.
• CustomerID must exist in the customer master.
• A delivered order must have a delivery date.

Step 6: Investigate before correcting

A value should not be deleted merely because it looks unusual. Contact the source owner, compare with related fields, inspect the original record, or compare with another system.

Step 7: Transform and validate

Apply documented transformations, then profile the resulting table again. The objective is not to make every column visually clean; it is to make the dataset defensible for its intended use.

4. Nulls and Missing Information

A null means that a value is absent or unknown. It is not automatically equivalent to zero, an empty string, “not applicable,” or “not recorded.” These meanings must be separated where they affect analysis.

Consider a DiscountAmount column:

• null may mean the discount was not supplied;
• zero may mean the transaction was assessed and no discount applied;
• an empty string may be a formatting problem; and
• “N/A” may mean the field is not relevant to the product.

Replacing every null with zero can distort revenue calculations. Conversely, leaving nulls untreated can cause totals, averages, or classifications to behave unexpectedly. The correct action depends on the data definition.

For a column with n rows, the missing-value rate can be expressed as:

Here, n is the number of rows in the defined profiling population. The measure is meaningful only when the population and definition of “missing” are explicit. For example, a blank string may need to be counted alongside nulls after text standardisation.

In Power Query, common actions include filtering nulls, replacing values, creating a missingness flag, or retaining nulls for later modelling. A missingness flag is often valuable because it preserves information about data collection. For example, EmailMissing = true may support operational follow-up without fabricating an email address.

5. Errors, Invalid Values, and Data Types

A Power Query error is different from an invalid business value. An error may occur when text such as ABC is converted to a number. An invalid business value may successfully load as a number but violate a rule, such as an age of −4 or a tax rate of 250%.

Type-related errors

Typical type problems include:

• dates stored in mixed formats;
• decimal numbers represented with different decimal separators;
• numbers containing currency symbols or thousands separators;
• Boolean values represented as Y, N, Yes, No, 1, and 0; and
• identifiers converted into numeric types.

Changing a type is not merely cosmetic. It determines how Power Query evaluates comparisons, sorting, grouping, joins, and calculations. Locale must be considered when interpreting dates and decimal separators. For instance, 03/04/2025 may mean 3 April or 4 March depending on the source convention.

A safe approach is to preserve the original field temporarily, create a typed version, and inspect conversion errors. In Power Query, try ... otherwise can be used in a custom column to prevent one bad value from stopping a transformation, but replacing errors with null without recording them may conceal a source problem.

Business-rule validation

Business rules can be implemented with conditional columns. For example, a simple validation classification might assign:

• Valid when quantity is positive and price is non-negative;
• Invalid quantity when quantity is zero or negative; and
• Invalid price when price is negative.

A row can be retained with a validation status rather than deleted. This supports auditability and allows valid records to be separated from exceptions in downstream reporting.

6. Duplicates and Uniqueness

Duplicate detection requires knowledge of the table grain and key. Repeated rows may be genuine. Two invoice lines can have the same product, quantity, and price but different line identifiers; two identical customer names may represent different people; and a daily snapshot intentionally repeats an entity across dates.

Distinguish three situations:

1. Exact duplicate: all relevant fields are repeated.
2. Duplicate key: the supposed key occurs more than once.
3. Possible duplicate entity: records differ slightly but may describe the same real-world object.

For a key column, a uniqueness ratio can be described as:

The ratio is between 0 and 1 when the denominator is positive. A ratio of 1 indicates that every non-null key value is distinct; it does not prove that the keys are correct or that null keys are acceptable.

In Power Query, grouping by a suspected key and counting rows is a practical method for locating repeated keys. Remove duplicates only after deciding which record is authoritative and what the grain should be. If duplicate rows represent repeated source deliveries, deduplication may be appropriate. If they represent legitimate events, removing them destroys business information.

7. Inconsistent Categories and Text Standardisation

Categorical inconsistency is common in manually maintained data. Differences may involve case, spaces, punctuation, abbreviations, spelling, or language. Examples include North, north , NORTH, and N..

A typical standardisation sequence is:

1. trim leading and trailing spaces;
2. clean non-printing characters where appropriate;
3. standardise case for comparison;
4. replace known abbreviations using a controlled mapping table;
5. validate against an approved reference list; and
6. retain the original value when auditability is required.

Blind replacement is risky. The text US may represent a country, a business unit, or an internal code. A mapping table maintained by the business is preferable to a long series of ad hoc replacements.

Reference data also supports conformance checks. A product code that does not match the product master may indicate a typo, a new product not yet loaded, or a retired code. The appropriate response may be correction, master-data update, or exception reporting.

8. Outliers and Descriptive Statistics

An outlier is an observation that is unusually distant from the rest of a distribution. Unusual does not mean wrong. A very large order may be a legitimate wholesale transaction, while an extra zero may indicate a data-entry error.

Useful descriptive statistics include:

• count and non-null count;
• minimum and maximum;
• mean;
• median;
• quartiles;
• standard deviation; and
• frequency counts for categorical fields.

For observations x₁, x₂, …, x_n, the arithmetic mean is:

The mean is sensitive to extreme values. The median is the middle ordered value, or the average of the two middle values when n is even. It is often more representative for highly skewed business measures such as order value.

The interquartile range is:

where Q₁ is the first quartile and Q₃ is the third quartile. A commonly used exploratory rule flags values below Q₁ − 1.5 × IQR or above Q₃ + 1.5 × IQR. This is a screening convention, not proof of an error. Quartile definitions can differ between software products, so the method should be documented.

Worked example

Suppose order values are 10, 12, 13, 15, 16, 18, 20, and 100. The mean is:

The value 100 increases the mean substantially, while the median is the average of the fourth and fifth ordered values: (15 + 16) ÷ 2 = 15.5. The difference suggests a right-skewed distribution. The correct business response is to investigate the 100 value, not automatically delete it.

Power Query can support outlier investigation through sorting, filtering, grouping, and custom calculations. More advanced statistical analysis may be performed in Python, R, or DAX, but the same principles of definition, context, and documentation apply.

9. Filtering as an Investigative Technique

Filtering is useful when it answers a defined question. Examples include:

• show rows where CustomerID is null;
• show order values greater than a business threshold;
• show dates outside the reporting period;
• show statuses not found in the approved list; and
• show products with multiple descriptions.

Filters should be applied carefully because they alter the population shown by later steps. A filter used for investigation should be clearly named and, where appropriate, removed from the production query. A filter used to exclude invalid records should have a documented rationale.

A robust investigation often uses a separate exception query. The main query contains records suitable for analysis, while the exception query retains rejected or questionable records with a reason code. This creates a useful balance between report usability and auditability.

10. A Practical Quality Assessment Framework

A concise quality register can include the following fields:

Severity should reflect impact, not merely volume. One invalid bank-account number may be more serious than hundreds of missing optional comments. A useful classification is critical, major, minor, or informational.

Quality metrics should be interpreted with thresholds agreed by stakeholders. For example, a 2% missing rate in a non-essential marketing field may be acceptable, while a 0.1% error rate in a regulatory identifier may be unacceptable.

11. Illustrative Case: Regional Sales Model

Background

A retail organisation receives monthly sales files from four regional offices. The files are combined in Power BI to produce a regional performance report. Each row is intended to represent one sales line.

Problem

The first refresh produces unexpectedly high sales for one region. The report owner suspects that the problem may arise from duplicated files, inconsistent currencies, or invalid quantities.

Analysis

The analyst profiles the combined query using the entire dataset rather than the default preview. Column distribution shows five region labels instead of four: North, South, East, West, and W est. Text inspection reveals a trailing space in some values. The Currency column contains USD, US$, and EUR. The sales amount column is numeric, but the source documentation confirms that the regional files use local currency.

Grouping by InvoiceLineID identifies 312 repeated identifiers. Investigation shows that the North file was loaded twice. However, some repeated identifiers occur legitimately because the source identifier is unique only within a file, not globally.

The Quantity column contains 17 negative values. Source review confirms that these are approved returns, so they should not be removed. The UnitPrice column contains four negative values that do not correspond to credits and are escalated to the finance team.

Approach and solution

The analyst:

1. trims and standardises region labels;
2. adds a source-file column;
3. creates a composite key from source system, invoice number, and line number;
4. removes the duplicate file load after confirmation;
5. retains negative quantities and classifies them as returns;
6. converts currencies using the approved monthly exchange-rate table;
7. flags negative prices as exceptions; and
8. creates an exception query containing all rejected or unresolved rows.

Outcome

The inflated regional total is corrected. Returns remain visible in net sales calculations, while invalid prices are excluded from the certified sales measure pending finance review. The organisation now has a repeatable profiling checklist and a quality register for each monthly refresh.

Learning points

The case demonstrates that profiling findings require business interpretation. A repeated identifier was not automatically a duplicate, and a negative quantity was not automatically an error. Source lineage, table grain, reference data, and business rules were necessary to reach defensible decisions.

12. Governance, Ethics, and Responsible Use

Data cleansing can create ethical and governance risks. Removing records may change reported performance, conceal operational failures, or disadvantage particular groups if missingness is not evenly distributed. Analysts should preserve source data, document transformations, restrict access to sensitive fields, and avoid using profiling outputs to make unjustified decisions about individuals.

Personally identifiable information should be profiled only when necessary. Names, email addresses, and identifiers should be handled according to organisational security policies and applicable privacy law. Profiling results themselves may reveal sensitive patterns, such as which customers have missing financial information.

Automated and AI-assisted cleansing tools can suggest matches, infer types, or identify anomalies. These suggestions must be reviewed because algorithms may standardise legitimate differences, introduce biased corrections, or produce plausible but unsupported values. AI can assist investigation; it should not replace data ownership, validation, or accountability.

13. Best Practices and Limitations

Effective practice includes:

• profile the complete dataset when making final decisions;
• define the row grain and keys before testing duplicates;
• preserve raw values and maintain query-step traceability;
• distinguish null, zero, blank, unknown, and not applicable;
• use reference tables for standardisation;
• treat outlier rules as screening tools rather than automatic deletion rules;
• retain exception records and reason codes;
• validate transformations with before-and-after row counts and totals;
• agree quality thresholds with business owners; and
• repeat profiling after source or query changes.

Power Query profiling also has limitations. Preview settings may not represent the full dataset. Statistical summaries may not explain causation. Power Query cannot establish real-world accuracy without an external reference. Large datasets may require performance-conscious profiling, incremental processes, or database-side checks. Finally, a clean-looking dataset can still contain systematic bias or incorrect business definitions.

Chapter Summary

Data profiling is a structured examination of data completeness, validity, consistency, uniqueness, distribution, and business-rule conformity. Power Query supports this work through column quality, column distribution, column profiles, filtering, grouping, type conversion, and custom validation logic. Nulls must be interpreted rather than blindly replaced. Errors differ from invalid business values, and duplicates can be identified only in relation to the intended table grain and key. Outliers require investigation because unusual values may be legitimate events. Standardisation is safest when supported by controlled reference data. A professional quality process preserves source data, records exceptions, documents decisions, and validates results after transformation. Profiling is therefore both a technical activity and a governance responsibility.

Key Terms

Data profiling: Systematic examination of data structure, content, and quality.

Data quality: The degree to which data is fit for a defined purpose.

Completeness: The extent to which required values are present.

Validity: Conformity with permitted formats, types, and rules.

Accuracy: Agreement between data and the real-world object or event.

Consistency: Agreement among related values, records, or systems.

Uniqueness: Absence of unintended repeated records or key values.

Integrity: Preservation of valid relationships between tables or entities.

Null: An absent or unknown value.

Data type: The computational interpretation assigned to a field, such as text, date, or number.

Data grain: The meaning represented by one row in a table.

Duplicate key: A key value appearing more than once where uniqueness is expected.

Outlier: An observation unusually distant from the distribution of other observations.

Quartile: A value dividing ordered data into portions for distribution analysis.

Interquartile range: The difference between the third and first quartiles.

Reference data: Authoritative values used to validate or standardise source data.

Exception query: A query containing records that fail defined checks or require review.

Data lineage: Information about a value’s source and transformation history.

Quality threshold: An agreed level at which a data issue requires action.

Review Questions

Short Answer Questions

1. What is the difference between a null value and a zero value?
2. Why must the profiling scope be checked before interpreting a column profile?
3. Define table grain and explain its relevance to duplicate detection.
4. What is the purpose of a reference table in data standardisation?
5. Why is an outlier not automatically a data error?

Long Answer Questions

1. Explain the dimensions of data quality and describe how Power Query can provide evidence for each dimension.
2. Describe a complete workflow for profiling a newly imported sales table in Power BI.
3. Compare exact duplicates, duplicate keys, and possible duplicate entities.
4. Explain how missing values, errors, and invalid business values differ, using examples.
5. Discuss the governance and ethical risks of automated data cleansing.

Analytical / Application Questions

1. A customer-status column contains Active, active , A, Inactive, and blank values. Design a Power Query investigation and propose a controlled standardisation approach.
2. A sales-value distribution has a mean of 25.5 and a median of 15.5. What might this indicate, and what additional checks should be performed before changing the data?
3. A table contains repeated order identifiers, but the source owner states that identifiers are unique only within each source file. Explain how you would design a reliable composite key and validate the resulting model.

Multiple Choice Questions with Answers

Question 1

Which Power Query feature is most directly used to inspect the frequency of distinct values in a column?

A. Column distribution

B. Column quality

C. Manage parameters

D. Refresh preview

Correct Answer: A. Column distribution

Question 2

Why should a product code such as 001245 usually be stored as text?

A. Text always uses less storage

B. It preserves leading zeros

C. Text automatically validates the code

D. It prevents all duplicates

Correct Answer: B. It preserves leading zeros

Question 3

Which statement about a null value is correct?

A. It always means zero

B. It always means not applicable

C. It represents absent or unknown information

D. It is identical to every blank string

Correct Answer: C. It represents absent or unknown information

Question 4

What must be understood before removing duplicate rows?

A. The report colour theme

B. The table grain and intended key

C. The number of dashboard pages

D. The user's screen size

Correct Answer: B. The table grain and intended key

Question 5

For the values 10, 12, 13, 15, 16, 18, 20, and 100, what is the mean?

A. 15.5

B. 20.4

C. 25.5

D. 100

Correct Answer: C. 25.5

Question 6

Which statement best describes the interquartile range?

A. It is the mean divided by the median

B. It is Q₃ minus Q₁

C. It is the maximum minus the minimum only

D. It is the number of distinct values

Correct Answer: B. It is Q₃ minus Q₁

Question 7

A negative quantity in a sales table is confirmed to represent an approved return. What is the most appropriate action?

A. Delete every negative quantity

B. Convert it to a positive quantity without documentation

C. Retain and classify it according to the business rule

D. Replace it with the column mean

Correct Answer: C. Retain and classify it according to the business rule

Question 8

What is a major risk of profiling only the first 1,000 rows?

A. The table cannot contain text

B. Issues later in the dataset may be missed

C. All dates become null

D. Duplicates are automatically removed

Correct Answer: B. Issues later in the dataset may be missed

Question 9

Which practice best supports auditability during data cleansing?

A. Overwrite the raw source

B. Delete all rejected records

C. Retain source values and document transformations

D. Replace every unusual value with the median

Correct Answer: C. Retain source values and document transformations

Question 10

What does a uniqueness ratio of 1 for non-null key values indicate?

A. Every non-null key value is distinct

B. Every key is accurate in the real world

C. There are no null values anywhere in the table

D. The table contains no business-rule violations

Correct Answer: A. Every non-null key value is distinct