Data Profiling and Quality Assessment

Introduction

Reliable business intelligence begins before data modelling, visualisation, or dashboard design. It begins with understanding whether the data is complete, valid, consistent, uniquely identifiable, and fit for its intended business purpose. Data profiling is the systematic examination of data values, structures, relationships, and patterns. Data quality assessment is the evaluation of those observations against technical expectations, business rules, and reporting requirements.

In Power BI, Power Query provides practical tools for this work. Column quality indicators, column distribution, column profiles, data-type inspection, value counts, error detection, duplicate analysis, and conditional rules allow an analyst to investigate data before applying transformations. Profiling is not merely a cleaning exercise. It is an analytical and governance activity that determines whether a dataset can support trustworthy decisions.

A technically valid value may still be inappropriate for a business purpose. For example, a negative sales quantity may be mathematically valid but unacceptable in a sales transaction table unless it represents a return. Similarly, a date stored as text may contain no missing values but still prevent correct time analysis. Effective profiling therefore combines automated inspection with domain knowledge.

Learning Objectives

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

• explain the purpose and scope of data profiling;
• distinguish completeness, validity, consistency, uniqueness, accuracy, and timeliness;
• use Power Query tools to inspect column quality, distribution, profiles, data types, and schema;
• calculate and interpret basic data-quality metrics;
• identify missing, invalid, inconsistent, duplicate, and potentially outlying records;
• translate business rules into testable data-quality checks;
• document profiling findings and assess their impact on a Power BI solution;
• evaluate limitations, governance requirements, and responsible uses of automated and AI-assisted profiling.

1. The Role of Profiling in the BI Lifecycle

A typical data workflow includes discovery, connection, profiling, transformation, modelling, validation, reporting, and monitoring. Profiling should occur early, but it is not a one-time activity. New source files, changed schemas, revised business processes, and late-arriving records can introduce new problems after the initial assessment.

Profiling operates at three related levels:

A source can pass structural checks and fail contextual checks. For instance, an OrderStatus column containing only text values may have a valid text type, yet values such as Completed, complete, and Closed may represent the same status inconsistently.

The output of profiling should be an evidence-based quality assessment. It should identify the issue, quantify its extent, locate affected records, assess business impact, and recommend an action. The appropriate action may be correction, standardisation, exclusion, quarantine, escalation to the source owner, or acceptance with a documented limitation.

2. Dimensions of Data Quality

Data quality is multidimensional. No single percentage can describe all aspects of a dataset.

2.1 Completeness

Completeness concerns whether required values are present. A value may be physically present but unusable, so completeness should be interpreted alongside validity.

For a column with N records and n_nonmissing non-missing records, the completeness rate is:

Here, n_nonmissing counts values that satisfy the agreed definition of non-missing, while N is the relevant population size. The measure assumes that the denominator is correctly defined and that blanks, nulls, whitespace-only strings, and placeholders such as N/A have been classified consistently.

A customer phone number may be optional, whereas a transaction date may be mandatory. Therefore, a quality target should be defined by field and business process rather than applied uniformly to every column.

2.2 Validity

Validity asks whether a value conforms to an allowed type, format, range, or domain. Examples include a date that can be interpreted as a date, a quantity that is numeric, and a product category that belongs to an approved list.

For n_valid valid values:

The assessed population may exclude genuinely optional missing values, or it may include them, depending on the rule being evaluated. The method must be documented.

2.3 Consistency

Consistency concerns agreement across records, columns, tables, and time periods. Examples include the same customer having one identifier in different systems, total sales agreeing with line-level calculations, and country codes following one convention.

Consistency is often relational. A row may be internally valid but inconsistent with a reference table. For example, a product code can have the correct text format but fail to match the current product master.

2.4 Uniqueness

Uniqueness concerns whether records or key fields are duplicated when they should identify one entity. A duplicate may be an exact duplicate row, a repeated business transaction, or two records representing the same customer with different spellings.

For a candidate key, uniqueness can be expressed as:

This measure is meaningful only when the candidate key is expected to be unique and when null-key treatment is specified. A composite key may be required, such as OrderID plus OrderLineNumber.

2.5 Accuracy

Accuracy refers to agreement with the real-world value. It cannot usually be established from the dataset alone. A postcode may have the correct format but belong to the wrong customer. Accuracy requires trusted reference information, reconciliation, sampling, or confirmation from the business process.

2.6 Timeliness and currency

Timeliness asks whether data is available when required, while currency asks whether it reflects the current state of the business. A daily inventory extract delivered two days late may be complete and valid but unsuitable for operational decisions.

2.7 Integrity

Integrity includes preservation of relationships and controls across systems. Referential integrity requires that foreign keys, such as ProductID in a sales table, match valid primary keys in a product table, subject to documented exceptions.

3. Power Query Profiling Capabilities

Power Query is both a transformation engine and an inspection environment. In Power BI Desktop, the View tools can expose column quality, column distribution, and column profile information. These features should be used as evidence, not treated as a substitute for business interpretation.

3.1 Column quality

Column quality commonly summarises the proportion of valid values, errors, and empty values. The interpretation depends on the current preview scope and the definition used by Power Query. Analysts should inspect the query settings and confirm whether profiling is based on the top rows or the complete dataset. A preview may conceal problems occurring later in a large source.

A column showing 100% valid values does not prove that the values are accurate or semantically appropriate. It indicates only that the current validation criteria have not identified missing or error values.

3.2 Column distribution

Column distribution displays distinct and unique values. It helps identify low-cardinality categorical columns, unexpectedly high-cardinality fields, repeated codes, and possible identifiers. A customer identifier with very few distinct values may signal a truncated extract or incorrect data type.

Distinct count and unique count have different meanings. Distinct count is the number of different values. Unique count is the number of values that occur exactly once. A column can contain many distinct values but few unique values if most values repeat.

3.3 Column profile

Column profiling provides statistics such as value counts, error counts, empty counts, minimum, maximum, and distribution information. Numeric summaries can reveal implausible ranges; text summaries can reveal unexpected lengths or patterns; date summaries can reveal gaps or future dates.

The profile is descriptive, not explanatory. A high maximum may be a legitimate large transaction, a unit conversion error, or an input mistake. Investigation requires record-level inspection and business context.

3.4 Data types and schema inspection

Correct data types are essential for reliable profiling and later modelling. Common Power Query types include text, whole number, decimal number, fixed decimal number, date, time, date/time, logical, and duration. Automatic type detection can be helpful for simple files but should not be accepted without review.

Schema inspection should consider:

• table and column names;
• data types and nullable status;
• required and optional fields;
• primary and foreign keys;
• column order and presence;
• expected relationships;
• source version and refresh date;
• encoding, locale, and date interpretation.

Locale is especially important. A value such as 04/05/2025 can represent different dates under different conventions. Explicitly setting the intended locale is safer than relying on automatic interpretation.

3.5 Profiling beyond the interface

Power Query can create diagnostic columns and summary queries. Useful techniques include grouping by a field to count records, adding conditional columns for rule failures, splitting columns to inspect components, extracting text length, and comparing tables using merges. Profiling queries should ideally be separated from production transformation queries so that investigation is reproducible and does not obscure the final dataflow.

4. A Systematic Profiling Process

A professional assessment follows a repeatable sequence.

Step 1: Define the purpose and population

Identify what the dataset represents, the reporting period, grain, source system, and intended decisions. Establish whether one row represents an order, order line, customer, account balance, or another business object. Many apparent quality problems are actually grain misunderstandings.

Step 2: Inspect structure and metadata

Record row count, column names, types, source location, refresh time, and expected keys. Compare the observed schema with the documented schema. Unexpected columns may indicate source changes; missing columns may break downstream logic.

Step 3: Profile individual columns

Review missing values, errors, distinct values, ranges, lengths, and common patterns. Pay particular attention to identifiers, dates, measures, and classification fields.

Step 4: Test relationships and business rules

Check referential integrity, allowed value lists, arithmetic relationships, date order, and cross-field dependencies. Rules should be explicit and traceable to a business requirement.

Step 5: Investigate exceptions

Filter to failing records and determine whether the issue is a source error, a valid exception, a transformation problem, or a misunderstanding of the rule. Do not delete exceptions merely because they are inconvenient.

Step 6: Classify and prioritise findings

A useful classification includes severity, affected rows, affected measures, root cause, owner, proposed action, and status. A missing optional email may be low risk; an incorrect sign on revenue may be critical.

Step 7: Document and monitor

Save profiling results, rule definitions, assumptions, and decisions. Repeat key checks during refreshes. A quality threshold that was satisfactory during development may fail when the source system changes.

5. Identifying Common Quality Problems

5.1 Missing values

Missingness can appear as null, empty text, whitespace, a zero, a sentinel such as -1, or a phrase such as Unknown. These representations have different meanings and must not be merged automatically. A zero quantity is not equivalent to an unknown quantity.

In Power Query, trim text before testing for empty strings, and inspect nulls separately from text placeholders. For required fields, create a rule-failure flag rather than silently replacing values. Imputation may be acceptable for some analytical fields, but it should be documented and should not manufacture precision.

5.2 Invalid values and errors

Invalid values violate a type, format, range, or domain rule. Examples include a malformed date, a negative age, an unrecognised currency code, or a percentage greater than 100 when the field is defined as a proportion.

Conversion errors should be preserved during investigation. Replacing errors immediately with null can hide the distinction between a missing value and an unconvertible value. A robust process retains the original field or an error indicator while creating a cleaned field.

5.3 Inconsistent formats and labels

Inconsistency includes extra spaces, case differences, punctuation, abbreviations, spelling variants, and mixed units. Standardisation should be based on an approved mapping, not arbitrary editing. For example, mapping UK, U.K., and United Kingdom requires confirmation that all refer to the same reporting category.

5.4 Duplicates

Begin by establishing the intended grain and key. Exact duplicate rows may arise from repeated file loads, while non-identical duplicates may represent legitimate updates or multiple events. Use grouping and counts to identify repeated keys, then inspect timestamps, status fields, and source-record identifiers before deciding which record, if any, is authoritative.

5.5 Outliers

An outlier is an observation that is unusually distant from the rest of a distribution. It is not automatically an error. A large order may be genuine, and removing it can distort business analysis.

For a numeric variable, the interquartile range is:

where Q₁ is the first quartile and Q₃ is the third quartile. A commonly used exploratory flag is:

Here, x is an observation. This is a screening convention, not a universal law. It assumes that a quartile-based rule is suitable for the analytical purpose and may be misleading for highly skewed, seasonal, bounded, or multimodal data. Business thresholds, peer-group comparisons, and time-series methods may be more appropriate.

6. Business Rules as Executable Quality Tests

Business rules convert organisational expectations into testable conditions. A rule should identify the field or fields, condition, exception policy, severity, and owner.

A rule can be represented as a Boolean test. For example, if q is quantity and t is transaction type, an allowed rule may be stated as:

The expression is applicable only when the organisation defines returns as negative or otherwise exceptional quantities. The rule should not be generalised to all sales systems.

In Power Query, conditional columns can label records as Pass, Fail, or Review. A three-state result is often better than a binary result because some cases require human judgement. Rule results should be aggregated into a quality report showing failure counts and rates.

7. Worked Example: Profiling a Sales Extract

Suppose a sales extract contains 10,000 rows. The OrderDate column contains 9,850 non-missing values, of which 9,820 are valid dates. The Quantity column contains 12 errors and 8 negative values; the business rule permits negative quantities only for returns, and three of the eight negative rows are returns. The ProductID column contains 9,600 distinct values, but 120 rows do not match the product master.

The date completeness is:

Date validity among all rows is:

If missing dates are treated as outside the valid population, validity among non-missing values is:

Both figures are correct for their stated denominators; they answer different questions.

For quantity, five negative values violate the rule because three are legitimate returns. The rule-failure rate, ignoring the 12 conversion errors for the moment, is:

The product referential-integrity failure rate is:

The analyst should not conclude that the extract is unusable from these numbers alone. Missing dates may prevent time-based analysis for 1.5% of rows, while unmatched products may affect category totals and product reporting. The next actions could include tracing missing dates to the source process, quarantining unmatched product records for master-data review, and separately handling conversion errors so they are not mistaken for ordinary missing values.

8. Applied Case: Regional Retail Reporting

Background

This illustrative case concerns a retailer combining point-of-sale transactions with a product master and store reference table to build a Power BI sales dashboard. Management expects daily sales, regional comparisons, product performance, and return analysis.

Problem

The initial refresh produced inconsistent regional totals. Some transactions were absent from product-category visuals, and the reported average selling price changed sharply from one refresh to the next.

Analysis

Profiling identified several issues:

• dates were imported using an incorrect locale for a subset of files;
• store codes contained leading spaces and two obsolete codes;
• product identifiers were numeric in one source and text in another;
• return transactions used negative quantities, but the model treated them as errors;
• duplicate files had been appended during a manual upload;
• a small number of unit prices were recorded in cents rather than currency units.

Column quality alone did not explain the problem. Most fields were non-null, but schema comparison, distribution checks, key matching, and business rules revealed structural and semantic defects.

Approach and solution

The team first documented the grain as one row per transaction line. It then standardised identifiers as text, trimmed codes, applied an explicit locale to dates, and created a controlled mapping for obsolete stores. Duplicate file loads were detected using source-file metadata and transaction keys. Return transactions were retained and classified by transaction type rather than discarded. Unit prices were checked against expected magnitude and reconciled with the source system.

Each rule produced a pass/fail/review flag. Records with unmatched products were placed in an exception report, while confirmed source corrections were sent to the data owner. The production query retained a small audit table containing refresh date, rule, failure count, and affected source file.

Outcome

Regional totals became reproducible, product-category coverage improved, and the average-price measure no longer fluctuated because of mixed units. Importantly, the solution did not claim that every record was perfect. It made limitations visible and gave owners a process for resolving them.

Learning points

The case demonstrates that quality is contextual, that identifiers require explicit type control, that legitimate business exceptions must be modelled rather than deleted, and that profiling should produce both cleaned data and an auditable explanation of what was changed.

9. Quality Reporting and Decision Thresholds

A quality report should show more than a traffic-light colour. It should include the rule, denominator, count of failures, percentage, severity, trend, and action owner. Thresholds should be agreed with stakeholders. For example, a 99% completeness target may be appropriate for an optional marketing attribute but inadequate for a financial posting date.

A simple weighted score can summarise several dimensions, but it should not replace dimension-level reporting. If quality dimensions have weights w_i that sum to 1 and scores s_i between 0 and 100, a weighted score is:

This score is meaningful only when the dimensions, weights, scales, and missing-data treatment are explicitly defined. A high aggregate score can conceal a critical failure in one field, so critical rules should be reported separately.

10. Governance, Ethics, and Responsible Automation

Data profiling can expose personal information, including names, addresses, contact details, and identifiers. Analysts should follow data-minimisation, access-control, retention, and approved-use requirements. Profiling outputs should avoid unnecessary exposure of sensitive values; masked samples and aggregated counts are often sufficient.

Quality rules can also create bias. If a field is systematically missing for a particular customer group because of unequal access or process design, simply excluding those records may produce unfair conclusions. Analysts should investigate the process causing missingness and communicate limitations.

AI-assisted profiling can suggest anomalies, mappings, or likely duplicates. Such suggestions are useful for prioritisation but require human validation. An AI system may infer an incorrect equivalence between labels, reveal sensitive data to an unauthorised service, or treat legitimate minority patterns as errors. Every automated recommendation should be traceable, reviewable, and tested against known examples.

11. Limitations and Best Practices

Profiling tools are limited by the data they can see. A sample-based preview may miss rare errors. Descriptive statistics cannot establish accuracy without external evidence. Outlier rules can misclassify valid events. Duplicate detection can fail when identifiers are absent or unstable. Automated type detection can be affected by locale and mixed formats.

Best practice is therefore to:

1. define the dataset grain and business purpose first;
2. inspect the complete population where feasible;
3. preserve raw data and retain an audit trail;
4. distinguish nulls, blanks, errors, and placeholders;
5. use explicit types, formats, locales, and reference lists;
6. test both individual columns and relationships between tables;
7. investigate exceptions before removing or replacing them;
8. measure rule failures with clear denominators;
9. separate profiling logic from production transformations where practical;
10. monitor quality across refreshes and assign ownership;
11. protect personal and confidential information;
12. validate automated and AI-assisted findings with domain experts.

Chapter Summary

Data profiling is the systematic examination of data structure, content, patterns, relationships, and business meaning. In Power Query, column quality, distribution, profiling, data-type controls, grouping, merging, and conditional rules provide a practical foundation for assessment.

The principal quality dimensions are completeness, validity, consistency, uniqueness, accuracy, timeliness, and integrity. These dimensions are related but not interchangeable. A dataset can be complete yet inaccurate, valid yet inconsistent, or unique yet untimely.

Effective profiling begins with purpose and grain, proceeds through schema and column inspection, tests business and relational rules, investigates exceptions, and documents actions. Missing values, invalid values, inconsistent labels, duplicates, and outliers require different treatments. Outliers should be investigated rather than automatically deleted, and legitimate business exceptions should be represented explicitly.

Quality reporting should disclose definitions, denominators, failure rates, severity, and ownership. Governance, privacy, fairness, and human oversight are essential, especially when automation or AI is used. The ultimate objective is not merely cleaner data but defensible, transparent, and fit-for-purpose business intelligence.

Key Terms

Data profiling: Systematic examination of data structure, values, patterns, and relationships.

Data quality: The degree to which data satisfies defined technical and business requirements.

Completeness: The extent to which required values are present.

Validity: Conformity to an allowed type, format, range, or domain.

Consistency: Agreement of values and meanings across records, columns, tables, or systems.

Uniqueness: The extent to which expected identifiers occur without inappropriate duplication.

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

Timeliness: Availability of data when it is needed.

Currency: How well data reflects the current state of the business.

Referential integrity: Valid correspondence between related keys in different tables.

Schema: The defined structure of a dataset, including fields, types, and relationships.

Cardinality: The number and pattern of distinct values in a column or relationship.

Null: A database representation of an absent or unknown value.

Outlier: An observation unusually distant from the rest of a distribution.

Business rule: A formal statement of an organisational requirement or constraint.

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

Exception: A record that fails a rule or requires special interpretation.

Data lineage: A record of the origin and movement of data through processes.

Data stewardship: Responsibility for the definition, quality, use, and governance of data.

Column profile: A descriptive summary of values, errors, empties, ranges, and distributions in a column.

Review Questions

Short Answer Questions

1. What is the difference between data profiling and data transformation?
2. Why can a column with 100% valid values still be unsuitable for a report?
3. Distinguish distinct count from unique count.
4. Why should nulls, blanks, and sentinel values be assessed separately?
5. What is the purpose of defining the grain of a dataset?

Long Answer Questions

1. Explain the major dimensions of data quality and show why no single quality percentage is sufficient.
2. Describe a systematic Power Query workflow for profiling a newly received sales extract.
3. Explain how business rules can be converted into executable quality tests.
4. Discuss the causes and treatment of duplicate records in a transactional dataset.
5. Evaluate the benefits and limitations of using interquartile-range rules to identify outliers.

Analytical / Application Questions

1. A dataset contains 25,000 rows, 24,250 non-missing customer IDs, and 23,900 IDs that match the customer master. Calculate the completeness rate and the referential-match rate using the appropriate denominators. Explain what each result means.
2. A sales table contains negative quantities for both returns and data-entry errors. Design a profiling rule that distinguishes legitimate returns from invalid negative sales and specify the evidence needed to validate it.
3. A Power BI refresh shows a sudden fall in regional sales. Propose a profiling investigation covering schema, dates, keys, duplicates, relationships, and business rules. Explain how you would distinguish a source-data problem from a transformation problem.

Multiple Choice Questions with Answers

Question 1

Which quality dimension asks whether required values are present?

A. Completeness

B. Uniqueness

C. Accuracy

D. Timeliness

Correct Answer: A. Completeness

Question 2

What is the main purpose of defining data grain before profiling?

A. To choose dashboard colours

B. To understand what one row represents

C. To remove all duplicate-looking values

D. To increase the number of columns

Correct Answer: B. To understand what one row represents

Question 3

A column contains 10,000 records, of which 9,500 are non-missing. What is its completeness rate?

A. 5%

B. 50%

C. 95%

D. 99.5%

Correct Answer: C. 95%

Question 4

Which Power Query feature is most directly useful for observing distinct and unique value patterns?

A. Column distribution

B. Merge queries

C. Append queries

D. Refresh preview

Correct Answer: A. Column distribution

Question 5

Why should a conversion error not automatically be replaced with null during investigation?

A. Nulls always improve accuracy

B. It can hide the difference between an error and a missing value

C. Power Query cannot process nulls

D. Errors are always legitimate values

Correct Answer: B. It can hide the difference between an error and a missing value

Question 6

Which situation is a referential-integrity failure?

A. A valid product code appears several times in a sales table

B. A product code in sales does not exist in the product master

C. A text field contains uppercase letters

D. A date column contains repeated dates

Correct Answer: B. A product code in sales does not exist in the product master

Question 7

Given Q₁ = 10 and Q₃ = 18, what is the upper exploratory IQR fence?

A. 20

B. 22

C. 26

D. 30

Correct Answer: C. 26

Question 8

Why should an outlier not automatically be deleted?

A. Every outlier is a duplicate

B. An outlier may represent a legitimate business event

C. Outliers cannot be identified numerically

D. Deleting outliers always increases completeness

Correct Answer: B. An outlier may represent a legitimate business event

Question 9

Which rule is a cross-field business rule?

A. Status must be text

B. Product code must be non-empty

C. Ship date must not precede order date

D. Region must have fewer than ten characters

Correct Answer: C. Ship date must not precede order date

Question 10

What is the most responsible use of AI-assisted anomaly detection?

A. Accept every suggested correction automatically

B. Use suggestions for prioritisation and validate them with domain experts

C. Expose all raw personal data to the AI system

D. Delete records that differ from the majority pattern

Correct Answer: B. Use suggestions for prioritisation and validate them with domain experts