Core Data Cleaning and Transformation

Introduction

Business intelligence is only as reliable as the data on which it is based. Operational data is commonly collected for transactions, administration, or system processing rather than analysis. Consequently, it may contain inconsistent data types, missing values, duplicate records, spelling variations, invalid dates, unnecessary columns, and values stored in unsuitable formats. Data cleaning and transformation convert this raw input into a consistent, interpretable, and analysis-ready structure.

In Power BI, these activities are primarily performed in Power Query. Power Query provides a repeatable sequence of transformations rather than a one-time manual edit. Each action is recorded as an applied step, usually represented internally in the M language. This reproducibility is central to professional data integration: when the source is refreshed, the same transformation logic can be executed again.

This chapter develops the practical foundations required to clean and standardize data. It focuses on type conversion, filtering, sorting, replacement, splitting, merging, formatting, null handling, deduplication, conditional logic, and standardization. It also considers data quality, governance, performance, and maintainability.

Learning Objectives

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

• explain why reproducible cleaning is essential in business intelligence;
• inspect and correct data types in Power Query;
• filter, sort, replace, split, merge, and format data appropriately;
• distinguish null, blank, zero, and invalid values;
• remove duplicates using an appropriate business key;
• create conditional columns and standardize inconsistent categories;
• document transformation decisions and assess their risks;
• apply a complete cleaning workflow to a realistic business scenario.

1. Data Cleaning as a Reproducible Process

Data cleaning is the identification and correction of inaccurate, incomplete, inconsistent, duplicated, or improperly structured data. Transformation is the broader process of changing data into a form suitable for analysis. Cleaning is therefore one important class of transformation.

A useful conceptual workflow is:

Profile → Decide → Transform → Validate → Document → Refresh

Profiling identifies problems. Decision-making determines what a value should mean and how it should be treated. Transformation implements the decision. Validation checks whether the result satisfies business rules. Documentation explains the logic, and refresh tests whether the process remains reliable when new data arrives.

Power Query records steps such as Changed Type, Filtered Rows, Replaced Value, and Removed Duplicates. The sequence matters. For example, filtering on a date before converting a text field to date may produce errors or incorrect comparisons. Similarly, removing duplicates before defining the correct key can delete valid transactions.

A transformation should be:

• deterministic: the same input produces the same output;
• traceable: the reason for each important step is understandable;
• reversible where possible: the original source remains available;
• appropriately scoped: cleaning logic is applied at the correct table or column level;
• validated: results are checked against business expectations.

A strong principle is to preserve raw source data and perform transformations in a query layer. Never overwrite the original source merely to make it appear clean.

2. Inspecting Data Before Transformation

Before changing data, examine its structure and quality. In Power Query, useful inspection activities include viewing column distribution, checking column quality, examining errors, reviewing distinct values, and identifying the number of rows and columns.

Questions to ask include:

1. What does one row represent?
2. Which columns identify a record uniquely?
3. Which fields are measures, attributes, identifiers, or dates?
4. What values are valid according to the business rules?
5. Are missing values meaningful or accidental?
6. Are different spellings representing the same category?
7. Are dates and numbers stored using the correct locale?

A column such as CustomerID may look numeric but should usually be treated as text. Leading zeros in an identifier—such as 001245—are meaningful and would be lost if the value were converted to a number. Conversely, a sales amount stored as text should be converted to a numeric type before aggregation.

Important Power Query data types

3. Type Conversion and Locale

Correct data types support valid filtering, sorting, calculations, relationships, and visualizations. A text value of 100 is not equivalent to a numeric value of 100 for analytical operations. Text sorting places 100 before 20 in some situations, whereas numeric sorting correctly recognizes magnitude.

Use the data type icon in the column header or the Transform > Data Type command. When converting dates or numbers, select Using Locale if the source format differs from the regional settings of the computer. For example, 31/12/2024 is interpreted differently from 12/31/2024 under different conventions.

A safe procedure is:

1. inspect sample values;
2. identify the source convention;
3. select the intended target type;
4. specify locale when necessary;
5. review conversion errors;
6. decide whether errors should be corrected, replaced, or removed.

Do not use error removal as a substitute for understanding the data. An error may indicate a defective source record, an unexpected but valid format, or a transformation applied in the wrong order.

4. Filtering and Sorting

Filtering limits rows to those satisfying a criterion. Typical filters include date ranges, valid status values, non-null records, positive quantities, or selected business units. Filtering can be applied to text, numbers, dates, and logical values.

A filter must reflect the analytical purpose. Removing cancelled orders may be suitable for a completed-sales report but inappropriate for an operational report measuring cancellation rates. Therefore, filtering is not merely a technical action; it is a semantic decision.

Sorting arranges rows for inspection or for a later operation. Sorting by date can help identify the latest record, but sorting alone does not establish which row is retained when duplicates are removed. If the intention is to retain the latest customer update, sort by customer identifier and update timestamp, then apply a carefully defined duplicate strategy.

Filtering and sorting do not change the meaning of individual values. They change the set or order of rows. They should therefore be distinguished from cleansing operations such as trimming, replacement, or type conversion.

5. Replacing Values and Standardizing Categories

Replacement is appropriate when an incorrect or inconsistent value has a known equivalent. Examples include changing N/A to null, replacing UK and U.K. with United Kingdom, or correcting a confirmed spelling error.

Power Query’s Replace Values command can replace exact values or, in some contexts, occurrences within text. Exact replacement is safer for categorical values because partial replacement may alter legitimate words. For example, replacing every occurrence of US could unintentionally modify a product description.

Standardization should be based on a controlled vocabulary. A mapping table is often preferable to a long chain of replacement steps. A mapping table may contain:

The source column can then be merged with the mapping table using a left outer join. This approach is easier to audit and extend. It also separates reference data from transformation logic.

Formatting operations commonly include:

• trimming leading and trailing spaces;
• cleaning non-printing characters;
• converting case using upper, lower, or proper case;
• standardizing punctuation;
• formatting phone numbers or postal codes;
• extracting year, month, or other date components.

Case conversion must be applied thoughtfully. Proper case may damage names such as McDonald, acronyms, or codes. Formatting should improve consistency without changing meaningful content.

6. Splitting and Merging Columns

Splitting divides one column into multiple columns. Common examples are separating Smith, Ana into surname and given name, separating a product code from a description, or extracting components from a date or address.

Splitting can be performed by delimiter, number of characters, or position. A delimiter-based split is appropriate only when the delimiter has a stable meaning. An address containing commas may not split reliably because the number of address components varies.

Merging combines columns into a new value. For example, first name and last name can be combined to create a display name. Before merging, handle nulls and separators carefully so that the result does not contain unwanted spaces or punctuation. Keep the original columns when they may be needed for filtering, matching, or governance.

A key distinction is between:

• merge columns: combine values from columns within the same row;
• merge queries: join rows from two tables using one or more matching fields.

7. Merging Queries and Joining Data

Merging queries is equivalent to joining tables in relational data processing. A join requires a matching key or set of keys. The key should be stable, sufficiently specific, and consistently formatted in both tables.

Common join types include:

A left outer join is often used to enrich a transaction table with attributes from a lookup table. An anti-join is valuable for data-quality investigation because it identifies unmatched codes.

Before merging, verify cardinality. If a customer table contains multiple rows for the same customer identifier, merging it into transactions may multiply transaction rows. This can inflate totals. A lookup table should normally contain one row per key unless a deliberate one-to-many relationship is intended.

After merging, expand only the required columns and check for null results. Unmatched rows may indicate new codes, spelling differences, type mismatches, or missing reference data.

8. Nulls, Blanks, Zeros, and Errors

These concepts must not be treated as interchangeable:

• null: no value is present or the value is unknown;
• blank text: a text value containing no visible characters;
• zero: a known numeric value equal to 0;
• error: a value that cannot be evaluated under the current transformation or type.

Replacing null with zero is valid only when the business meaning is “none” rather than “unknown.” For example, zero units sold may be appropriate when a confirmed transaction contains no units, but it is not an appropriate replacement for an unrecorded quantity.

Possible null-handling strategies include:

1. retain null and allow downstream measures to handle it;
2. replace with a business-defined default;
3. replace with an explicit category such as Unknown;
4. remove rows when the record is unusable;
5. investigate the source system.

For numeric columns, replacing null with the mean or median can introduce artificial information and should not be done automatically. Imputation requires a justified analytical method and clear documentation.

Errors can be handled using Remove Errors, Replace Errors, or conditional logic. The preferred option depends on the cause. For a corrupted row that cannot be repaired, exclusion may be justified; for a predictable format variation, transformation logic should be improved instead.

9. Deduplication and Record Identity

Duplicate removal is safe only when “duplicate” has been defined. Two rows may have identical visible values but represent two legitimate transactions. Conversely, rows may differ in a timestamp while representing repeated imports of the same business event.

Possible keys include:

• transaction ID;
• customer ID plus effective date;
• product code plus warehouse;
• a composite key created from several columns.

Select the columns that define uniqueness, then use Remove Duplicates. If one record should be preferred, establish the rule before deduplication—for example, retain the most recent valid update or the record from the authoritative system.

Deduplication should be validated by comparing row counts before and after the operation and by examining removed records when possible. It is often useful to create an exception query that stores suspected duplicates rather than deleting them without evidence.

10. Conditional Logic and Derived Columns

Conditional columns classify records according to business rules. For example:

• if sales amount is at least 10,000, classify as High Value;
• if delivery date is null, classify as Pending;
• if quantity is less than zero, classify as Invalid.

Conditions should be ordered from the most specific to the most general. Consider a classification with thresholds. A value of 12,000 satisfies both “at least 5,000” and “at least 10,000”; placing the lower threshold first would produce the wrong category.

Use explicit rules and document boundary conditions. Decide whether a threshold is inclusive or exclusive. For example, “at least 10,000” includes 10,000, while “greater than 10,000” does not.

Conditional logic can be created through the graphical interface or with M expressions. A conceptual M pattern is:

if [Amount] >= 10000 then "High Value" else "Standard"

Complex logic may be easier to maintain when represented as a reference table of ranges or categories rather than a long nested expression.

11. A Professional Cleaning Workflow

A practical sequence for many source tables is:

1. connect to the source and preserve the original query;
2. remove irrelevant columns only after confirming their meaning;
3. rename columns using clear, stable names;
4. trim and clean text fields;
5. convert data types using the correct locale;
6. standardize categorical values;
7. handle nulls and errors according to business rules;
8. create derived or conditional columns;
9. merge reference data where required;
10. filter invalid records or isolate them for review;
11. define and remove duplicates;
12. reorder and document final columns;
13. validate row counts, totals, keys, and unmatched records.

The best sequence depends on the source. For example, trimming a key column should occur before joining tables, and type conversion should occur before date filtering. Avoid unnecessary steps because every step increases refresh complexity and possible failure points.

Applied Case: Regional Retail Sales Integration

Background

This illustrative case concerns a retailer that receives monthly sales files from three regional offices. Each file contains OrderID, OrderDate, CustomerCode, Region, Quantity, UnitPrice, and Status. A separate customer reference table contains CustomerCode, CustomerName, and CustomerSegment.

Problem

The regional files contain the following issues:

• dates use both DD/MM/YYYY and MM/DD/YYYY formats;
• prices contain currency symbols and are stored as text;
• region values include North, north, and NORTH ;
• some customer codes have leading or trailing spaces;
• cancelled orders are included in the files;
• one file contains a repeated extract of several orders;
• some customer codes do not appear in the reference table.

Analysis and approach

First, the analyst confirms that each row represents one order line and that OrderID plus product information defines uniqueness. CustomerCode is retained as text because leading zeros are meaningful.

The files are combined using Append Queries, because they have the same business structure. Text fields are trimmed and region values are converted to a consistent case. Dates are converted using the appropriate locale for each source, rather than relying on the computer’s default regional settings. Currency symbols are removed only after confirming that they are formatting characters rather than part of the product description.

The status field is standardized, and cancelled rows are filtered out for the completed-sales dataset. A separate query retains cancellations for operational reporting. This prevents one analytical purpose from destroying data needed for another.

The customer table is cleaned in the same way, especially its key column. A left outer merge enriches sales with customer attributes. An anti-join is then used to identify sales whose customer codes have no reference match. These records are not silently deleted; they are reported as data-quality exceptions.

Repeated order lines are investigated. If the repetition is confirmed to be a duplicate file extract, duplicates are removed using the agreed business key. The process is documented in a query description and in the project’s data-quality log.

Outcome

The resulting sales query contains consistent dates, numeric quantities and prices, standardized regions, valid customer joins, and a clearly defined treatment of cancellations. The exception query lists unmatched customer codes and suspected duplicates. At the next monthly refresh, the same steps execute automatically, allowing the team to detect newly introduced issues.

Learning points

The case demonstrates that cleaning is not equivalent to deleting inconvenient values. It requires understanding business meaning, preserving alternative views of the data, controlling join behavior, and creating an auditable process.

Quality Assurance, Governance, and Professional Practice

A clean-looking table may still be analytically wrong. Validation should include:

• row-count comparison before and after major steps;
• totals of key measures before and after filtering;
• checks for nulls in required fields;
• checks for duplicate business keys;
• validation of minimum and maximum dates;
• review of unmatched join records;
• confirmation that categories belong to an approved vocabulary;
• refresh testing with new source files.

Data cleaning can affect privacy and governance. Avoid exposing personal information unnecessarily, restrict access to sensitive fields, and maintain a record of transformations that alter customer, employee, or financial data. Where decisions depend on classifications or exclusions, the organization should be able to explain the rules applied.

AI-assisted features may help identify anomalies, suggest transformations, or infer data types. However, suggestions must be reviewed by a knowledgeable analyst. Automated standardization can incorrectly merge distinct entities, and anomaly detection can mistake legitimate unusual transactions for errors. Human approval, source validation, and auditability remain necessary.

Performance also matters. Filter early when it is logically safe, remove unused columns, avoid repeated expensive operations, and perform joins on clean, compatible keys. Query folding may allow transformations to execute in the source system, improving performance; however, folding behavior depends on the connector and transformation. Always test refresh duration and results rather than assuming that a step will fold.

Best-Practice Checklist

• Define the grain of every table before cleaning it.
• Preserve raw data and create a separate transformation layer.
• Treat identifiers as identifiers, not automatically as numbers.
• Specify locale for ambiguous dates and numeric formats.
• Use mapping tables for substantial category standardization.
• Distinguish null, blank, zero, and error.
• Confirm key cardinality before merging queries.
• Define duplicate rules before removing duplicates.
• Keep exception outputs for rejected or unmatched records.
• Validate totals, row counts, keys, and refresh behavior.
• Prefer transparent, named, and documented steps.
• Reassess transformations when source-system structures change.

Chapter Summary

Core data cleaning and transformation in Power Query is a controlled process for converting operational data into reliable analytical data. The process begins with profiling and understanding the meaning of rows, columns, keys, and valid values. Correct data types are essential for accurate filtering, sorting, calculations, joins, and reporting, with locale settings being particularly important for dates and numbers.

Filtering and sorting alter the selected or ordered records, while replacement, formatting, splitting, and merging alter or restructure values. Nulls, blanks, zeros, and errors have different meanings and must be handled according to business rules. Deduplication requires a defensible definition of record identity, and joins require compatible keys and verified cardinality. Conditional columns provide repeatable classifications, while mapping tables support maintainable standardization.

Professional Power Query solutions preserve source data, use sequenced and documented steps, validate outcomes, retain exceptions, and test refreshes. The objective is not merely a visually tidy table but a trustworthy, explainable, and reusable transformation process.

Key Terms

Data cleaning: Identifying and correcting inaccurate, incomplete, inconsistent, or unsuitable data.

Data transformation: Changing data structure or values to support analysis.

Power Query: Microsoft’s environment for connecting to, shaping, and combining data.

Applied step: A recorded transformation in a Power Query query.

Data type: The declared interpretation of a value, such as text, date, or decimal number.

Locale: Regional conventions governing formats such as dates, decimal separators, and currency.

Filtering: Selecting rows that satisfy specified criteria.

Sorting: Ordering rows according to one or more columns.

Standardization: Converting equivalent representations into a consistent form.

Mapping table: A reference table that relates source values to approved standard values.

Split column: Dividing one column into multiple columns.

Merge columns: Combining values from columns in the same row.

Merge queries: Joining rows from two tables using matching fields.

Null: An absent or unknown value.

Data error: A value that cannot be evaluated under the current transformation or type.

Business key: A field or combination of fields that identifies a business record.

Deduplication: Identifying and removing or isolating repeated records.

Conditional column: A derived column produced according to specified rules.

Query folding: Delegating compatible transformations to the source system.

Data lineage: The traceable history of where data came from and how it was transformed.

Review Questions

Short Answer Questions

1. Why should raw source data normally be preserved when using Power Query?
2. What is the difference between a null value and a zero?
3. Why might an identifier such as 001245 be stored as text?
4. What is the purpose of an anti-join?
5. Why is locale important when converting dates?

Long Answer Questions

1. Explain a complete, reproducible workflow for cleaning a new operational data source in Power Query.
2. Discuss the risks of removing duplicates without first defining a business key.
3. Compare replacement steps with the use of a mapping table for standardizing categories.
4. Explain how nulls, blanks, zeros, and errors should be treated differently in an analytical dataset.
5. Discuss the importance of join cardinality and key quality when merging queries.

Analytical / Application Questions

1. A sales file contains OrderDate values in two regional formats, currency symbols in price fields, inconsistent region names, and unmatched customer codes. Design a sequence of Power Query steps and justify the order.
2. A customer table contains three records for one customer code. Explain how merging it with a transaction table could affect totals and propose a method for resolving the issue.
3. An organization wants to remove all rows with null sales quantities by replacing them with zero. Evaluate this decision and recommend validation questions before implementation.

Multiple Choice Questions with Answers

Question 1

Which Power Query data type is generally most appropriate for an identifier that contains leading zeros?

A. Whole Number

B. Text

C. Decimal Number

D. Logical

Correct Answer: B. Text

Question 2

What is the primary purpose of specifying a locale during type conversion?

A. To remove duplicate rows

B. To determine how regional date and number formats are interpreted

C. To create a relationship between tables

D. To sort rows alphabetically

Correct Answer: B. To determine how regional date and number formats are interpreted

Question 3

Which join keeps every row from the first table and matching rows from the second table?

A. Inner join

B. Full outer join

C. Left outer join

D. Left anti join

Correct Answer: C. Left outer join

Question 4

Why should null not automatically be replaced with zero?

A. Zero is always stored as text

B. Null and zero may represent different business meanings

C. Power Query cannot replace nulls

D. Zero values cannot be aggregated

Correct Answer: B. Null and zero may represent different business meanings

Question 5

What is the main purpose of a mapping table?

A. To store passwords for data sources

B. To define approved equivalents for inconsistent source values

C. To calculate visual-level filters

D. To change a table into a chart

Correct Answer: B. To define approved equivalents for inconsistent source values

Question 6

Before removing duplicates, an analyst should first determine:

A. The screen resolution of the report

B. The business definition of a unique record

C. The number of report pages

D. The preferred chart colour

Correct Answer: B. The business definition of a unique record

Question 7

Which operation identifies rows in the first table that have no matching row in the second table?

A. Left anti join

B. Inner join

C. Right outer join

D. Merge columns

Correct Answer: A. Left anti join

Question 8

Why is the order of conditional rules important?

A. Power Query always evaluates rules alphabetically

B. A general rule placed first may capture values intended for a more specific rule

C. Rules can only contain text values

D. Conditional columns cannot contain thresholds

Correct Answer: B. A general rule placed first may capture values intended for a more specific rule

Question 9

Which action combines values from columns within the same row?

A. Merge columns

B. Append queries

C. Inner join

D. Remove errors

Correct Answer: A. Merge columns

Question 10

What is a key benefit of retaining an exception query?

A. It hides all data-quality problems from users

B. It preserves records requiring investigation instead of silently deleting them

C. It guarantees that every join will match

D. It converts all values to numbers

Correct Answer: B. It preserves records requiring investigation instead of silently deleting them