Data Preparation for Modelling

Introduction

A reliable Power BI report begins before visual design and before most DAX measures are written. It begins with data preparation: inspecting source data, identifying quality problems, applying reproducible transformations, and integrating related sources into a coherent analytical structure. In Power BI, these activities are primarily performed in Power Query, while calculations that depend on the model and user filter context are generally performed with DAX.

The distinction is important. A transformation that removes invalid rows, standardises text, or combines files changes the data loaded into the model. A DAX measure, by contrast, normally leaves the stored data unchanged and calculates a result when a report visual is evaluated. Poorly prepared data can produce inaccurate relationships, misleading totals, slow refreshes, and unnecessarily complex DAX.

This chapter develops a practical foundation for profiling, cleaning, transforming, and integrating data in Power Query. It also explains how to decide whether a requirement belongs in Power Query or DAX.

Learning Objectives

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

• explain the role of Power Query in an analytical workflow;
• profile data for completeness, validity, consistency, and uniqueness;
• clean and transform columns using appropriate Power Query operations;
• combine data by appending rows and merging columns from related tables;
• use data types, keys, and query dependencies correctly;
• distinguish refresh-time Power Query transformations from model-time DAX calculations;
• evaluate common data-preparation risks and document a reliable solution.

1. The Preparation-to-Model Workflow

Power BI commonly follows this sequence:

1. Connect to files, databases, web services, or other sources.
2. Profile and inspect the incoming data.
3. Transform data using Power Query.
4. Load the resulting tables into the semantic model.
5. Create relationships, measures, and calculated columns using modelling features and DAX.
6. Build reports and validate results with business users.

Power Query uses the M language. The graphical interface creates M expressions behind the scenes, and advanced users may edit those expressions directly. Each query consists of a sequence of applied steps. The result of one step becomes the input to the next, making the transformation process inspectable and repeatable.

A useful conceptual separation is:

The table is a guide rather than an absolute rule. A calculated column can be created in DAX, but if its logic is based only on source-row values and does not require model context, implementing it in Power Query is often more efficient and easier to audit.

2. Understanding Data Quality

Data quality is multidimensional. A dataset can be complete but invalid, or valid but inconsistent. The following dimensions are especially important in business intelligence:

• Completeness: required values are present.
• Validity: values conform to an allowed type, range, or format.
• Accuracy: values represent the real-world object or event correctly.
• Consistency: the same concept is represented in the same way across sources.
• Uniqueness: identifiers do not contain unintended duplicates.
• Timeliness: data reflects the period required for the decision.
• Integrity: relationships between entities are preserved.

For example, a customer table may contain no blanks in CustomerID, yet still be defective if the same customer appears with several identifiers. A sales table may have valid dates and amounts but contain duplicate transactions, causing revenue to be overstated.

2.1 Data profiling

Power Query provides profiling features such as column quality, column distribution, and column profile. These help reveal errors, empty values, distinct values, and value frequencies. Profiling should be performed before deleting or replacing data because an apparent error may represent a meaningful business condition.

A practical profiling sequence is:

1. Inspect the number of rows and columns.
2. Review column names and inferred data types.
3. Check null, blank, and error values.
4. Examine minimum, maximum, and representative values.
5. Count distinct values and compare them with expected uniqueness.
6. Inspect unusual spellings, spaces, casing, and punctuation.
7. Compare date ranges with the intended reporting period.
8. Check whether numeric values have plausible units and signs.
9. Test candidate keys and relationships.

Profiling results should be recorded in a data-quality log. This supports reproducibility and provides evidence for decisions such as excluding a row, replacing a value, or escalating an issue to the source-system owner.

3. Types, Nulls, Errors, and Missing Values

3.1 Data types

Correct data types are foundational. Common Power Query types include text, whole number, decimal number, fixed decimal number, date, datetime, time, logical, and binary. A date stored as text may sort alphabetically rather than chronologically. A sales amount stored as text cannot be reliably aggregated. A product code that contains leading zeroes must usually remain text.

Type conversion should be deliberate. Automatic type detection can be useful for exploration, but production queries should explicitly define important types. For example, CustomerID may be text, Quantity may be a whole number, UnitPrice may be a decimal number, and OrderDate may be a date.

3.2 Null, blank, and zero

These values are not interchangeable:

• Null means that a value is missing or unknown.
• Blank text is a text value containing no characters.
• Zero is a known numeric value.

Replacing null sales quantities with zero may be appropriate only if the business confirms that missing means no quantity. Otherwise, the replacement invents information. Similarly, replacing a missing discount with zero can misstate the difference between “no discount” and “discount not recorded.”

3.3 Errors

Errors can arise from invalid type conversion, malformed dates, division by zero, or unexpected source values. Power Query allows errors to be removed, replaced, or retained for investigation. Removing all error rows is not automatically good practice: it can conceal a systematic source problem. A better approach is often to create an error-reporting query or preserve an exception column while producing a clean analytical table.

4. Core Power Query Transformations

4.1 Selecting, removing, and renaming columns

Remove columns that are irrelevant, duplicated, sensitive, or technically unnecessary. Retain columns needed for analysis, relationships, auditing, or future requirements. Renaming should improve clarity without changing business meaning. Names such as OrderDate, ProductKey, and NetSales are preferable to ambiguous names such as Date1, Code, and Value.

Column removal can reduce model size, but it should not eliminate useful audit fields without agreement. A source transaction number may not be displayed in a report but can be essential for reconciliation.

4.2 Filtering rows

Filters can remove test records, cancelled transactions, or records outside the required period. Filtering requires a documented business rule. For example, excluding orders with status “Cancelled” may be correct for shipped-sales reporting but incorrect for an order-cancellation analysis.

Avoid filtering away unknown categories merely because they look untidy. An “Unknown” member may be preferable to silently losing fact rows.

4.3 Text standardisation

Common operations include trimming leading and trailing spaces, cleaning non-printing characters, changing case, replacing punctuation, and splitting or extracting text. Standardisation is particularly important before joining tables. London, london, and London may be visually similar but can behave as different text values in a merge.

Text cleaning must preserve meaningful distinctions. Product codes may be case-sensitive in the source system, and punctuation may carry information. Establish the rule with business and system owners.

4.4 Dates and time

Date fields should be converted to appropriate date or datetime types. Extracting year, quarter, month number, or weekday can support analysis, although a dedicated date table is generally preferable for model-level time intelligence. Be cautious with time zones: a timestamp recorded in UTC may belong to a different local business date after conversion.

4.5 Replacing values and conditional columns

Replacing values is appropriate when a documented mapping exists, such as converting N, No, and False to a common logical representation. A conditional column can classify rows according to rules, such as assigning a sales band. Conditions should be ordered carefully because the first matching condition may determine the result.

4.6 Custom columns and row-level arithmetic

Power Query custom columns can calculate values from fields in the same row, such as gross line value:

Here, Quantity is the number of units and UnitPrice is the monetary price per unit. The result has currency units, assuming quantity is measured in units and price is currency per unit. If discounts, tax, returns, or currency conversion are relevant, the business definition must be specified before implementing the expression.

A row-level calculation is often a good candidate for Power Query because it is computed during refresh and can be reused by all report visuals. A ratio such as overall profit margin is usually better as a DAX measure because it must aggregate profit and revenue in the current filter context before dividing.

5. Combining Data: Append and Merge

5.1 Append

Appending adds rows from one table below another. It is appropriate when tables have the same business grain and broadly compatible columns—for example, January, February, and March sales files. The operation resembles a vertical union.

Before appending, confirm:

• columns represent the same concepts;
• units and currencies match;
• data types are compatible;
• duplicate rows are understood;
• file names or source periods are retained where useful;
• column names are standardised.

Appending tables with similarly named but semantically different columns creates a misleading dataset. “Revenue” in one file may be gross revenue while “Revenue” in another is net revenue.

5.2 Merge

Merging combines columns from two queries using one or more matching fields. It is conceptually similar to a relational join. A sales fact table may be merged with a product lookup to obtain category and brand.

A merge depends on key quality. The lookup side should normally contain one row per key. If a product key occurs three times in the lookup, a one-to-many match can produce multiple output rows for one sales record, inflating totals. Always check uniqueness before expanding merged columns.

Common join types include:

A left outer merge is often safest for fact data because it preserves transactions even when a lookup match is missing. Unmatched keys should then be investigated rather than silently discarded.

5.3 Relationship modelling versus merging

Not every relationship should be implemented by physically merging columns. In a star schema, a fact table can remain separate from dimension tables, with a model relationship connecting them. Merging may be appropriate for small, stable lookup attributes, but retaining dimensions separately usually supports clearer modelling, reuse, and controlled filtering.

The decision depends on grain, refresh architecture, source system, and model design. Avoid denormalising merely because it appears simpler; repeated attributes can increase storage and create inconsistent updates.

6. Query Dependencies, Parameters, and Reuse

The query dependency view shows how queries relate to one another. A robust solution often uses staging queries for source connection and basic standardisation, referenced queries for reusable logic, and final queries for tables loaded into the model.

Parameters can store values such as a folder path, reporting date, or environment name. This avoids editing multiple steps when moving from development to production. Folder-based ingestion can combine files using a consistent transformation function, but files must be validated for compatible structure.

Query folding occurs when Power Query translates transformations into operations executed by the source system, such as a SQL database. Folding can improve performance because the source filters or aggregates data before transmission. Not every transformation folds, and folding behaviour depends on the connector and operation. When performance matters, apply foldable filters and column selection early, and verify folding using the relevant Power Query diagnostics or native-query options where available.

7. Power Query and DAX: A Decision Framework

Power Query and DAX solve different classes of problems.

Prefer Power Query when:

• the operation is required for every refresh;
• the logic depends only on source-row values;
• data must be cleaned, reshaped, combined, or typed;
• rows or columns should be removed before loading;
• the operation can reduce model size;
• the result should be reused as prepared data.

Prefer a DAX measure when:

• the result must respond to slicers and filters;
• aggregation logic depends on evaluation context;
• the calculation compares periods or evaluates rankings;
• the value should not be stored for every row;
• users need a dynamic KPI.

For example, a margin measure can be expressed conceptually as:

[Total Profit] and [Total Sales] are measures evaluated in the current filter context. DIVIDE is preferred to the / operator because it provides controlled handling when the denominator is zero or blank. The measure is not a row-level margin averaged across transactions; it is a ratio of aggregated values.

A DAX calculated column is evaluated for each model row during refresh and stored in the model. It can be useful when a row-level attribute is needed for filtering or relationships, but it may increase model size. A Power Query custom column is created before loading and is often preferable when the logic does not require DAX context.

8. Worked Example: Preparing a Sales Dataset

Suppose a retailer receives a file with the following fields: Order ID, Order Date, Product Code, Qty, Price, Region, and Status.

Initial inspection finds:

• Order Date is text in mixed formats;
• Product Code contains trailing spaces;
• Qty is numeric except for two values marked N/A;
• Status contains Complete, Completed, and Cancelled;
• one product code is missing from the product lookup;
• the file contains a test transaction.

A defensible preparation process is:

1. Rename columns to consistent names.
2. Trim Product Code and standardise status values using an approved mapping.
3. Convert Order Date using the correct locale and retain conversion errors for review.
4. Convert Qty to a whole number; place invalid values in an exception output rather than silently treating them as zero.
5. Remove the test transaction using an explicit source flag or documented identifier.
6. Decide whether cancelled rows belong in the target reporting definition.
7. Append other monthly files only after confirming identical grain and units.
8. Merge the product lookup using Product Code.
9. Use an anti-join or unmatched indicator to report the missing product key.
10. Load the cleaned fact table and separate product dimension into the model.
11. Reconcile row counts and sales totals against the source control report.

This process demonstrates that cleaning is not simply cosmetic. Each step affects the meaning, completeness, and auditability of the final model.

9. Illustrative Case: Regional Retailer Reporting Project

Background

This illustrative case concerns Northstar Retail, a fictional retailer operating in three regions. Its finance team receives separate monthly spreadsheet exports, while the product master is maintained in an enterprise database. Management wants a Power BI report showing sales, returns, product categories, and regional performance.

Problem

The monthly files use different column names, contain inconsistent region labels, and include product codes with spaces. Some files contain cancelled transactions, and the product master includes duplicate historical records. Initial reports showed higher sales after the product merge, suggesting row multiplication.

Analysis

The team profiled each file and compared row counts before and after every major step. A uniqueness test showed that the product master was not unique by product code because retired versions remained active. A merge therefore matched some sales rows to multiple product records. The team also found that one region used South-East, while another used Southeast.

Approach and Solution

The team created a staging query for each source, standardised names and types, and used a controlled region mapping. Monthly sales were appended after confirming that all files represented one row per order line. A product-dimension query selected the current record for each product code according to an approved effective-date rule. The cleaned product dimension was then checked for one row per key.

A left outer merge was used only for diagnostic purposes. Unmatched product codes were sent to an exception table, while the model retained the sales rows. The final model used a sales fact table, product dimension, date dimension, and region dimension. Dynamic sales and margin calculations were implemented as DAX measures rather than stored row-level totals where aggregation context mattered.

Outcome

After correction, total sales reconciled to the finance control report, and the number of product matches became predictable. The report distinguished missing product master data from genuine zero sales. Refresh documentation recorded source locations, mapping rules, exception counts, and validation totals.

Learning Points

The case illustrates five principles: profile before transforming; protect fact-row grain; validate lookup uniqueness; preserve exceptions for investigation; and use DAX for context-dependent analytical calculations.

10. Challenges, Governance, and Best Practices

Data preparation involves governance as well as technical operations. Customer names, addresses, and identifiers may be personal data. Access should follow organisational policies and applicable privacy law. Remove or mask unnecessary personal fields, restrict workspace permissions, and avoid exporting sensitive data into unsecured files.

Transformation logic should be documented. At minimum, record the source, refresh frequency, business definitions, data-owner contacts, mappings, exclusions, and validation controls. Version control or a controlled deployment process is valuable for production solutions.

Common failure modes include:

• relying on automatic type detection without review;
• deleting errors without measuring their frequency;
• replacing nulls with zero without a business rule;
• merging on non-unique keys;
• appending files with different grains;
• applying the same transformation manually in many queries;
• using DAX to repair fundamentally dirty source data;
• hiding unmatched records instead of reporting them;
• failing to test refresh behaviour when new files arrive.

A strong preparation process includes automated or repeatable checks for row counts, duplicate keys, unmatched keys, null rates, date ranges, and control totals. Data quality is not a one-time activity: source systems, business rules, and reporting requirements change.

Contemporary Developments

Modern Power BI solutions increasingly use dataflows, lakehouses, warehouses, and reusable transformation layers to centralise preparation. These approaches can reduce duplicated logic across reports. However, centralisation does not remove the need for profiling and validation. AI-assisted features may suggest transformations or detect patterns, but suggestions require human review, especially where financial, regulatory, or personal-data consequences exist. An apparently convenient automated correction can encode an unjustified assumption.

Best-Practice Checklist

Before loading a prepared query, ask:

• Are all important columns explicitly typed?
• Are nulls, blanks, and zeros interpreted correctly?
• Are transformations documented and reproducible?
• Has the original grain been preserved?
• Are candidate keys unique where required?
• Are append sources structurally and semantically compatible?
• Have unmatched merge keys been quantified?
• Are sensitive fields necessary and appropriately protected?
• Is the logic in Power Query or DAX for a defensible reason?
• Have totals and row counts been reconciled with an authoritative source?

Chapter Summary

Power Query provides the principal environment for profiling, cleaning, reshaping, and integrating data before it enters a Power BI model. Effective preparation depends on explicit data types, careful treatment of nulls and errors, standardised text, controlled date handling, and documented business rules. Append operations stack compatible rows, whereas merge operations add columns through matching keys; both require attention to grain and key uniqueness.

A well-designed preparation process preserves useful records, exposes exceptions, and supports reconciliation. Query dependencies, parameters, reusable functions, and query folding can improve maintainability and refresh performance. Power Query is generally appropriate for refresh-time transformations based on source data, while DAX measures are appropriate for calculations that must respond to filter and evaluation context. The distinction reduces model complexity and supports trustworthy analysis.

Key Terms

Power Query: The data-connection and transformation environment in Power BI.

M language: The functional language used by Power Query to express transformations.

Data profiling: Systematic inspection of data quality, structure, and distributions.

Data type: The defined kind of value stored in a column, such as date, text, or decimal number.

Null: A missing or unknown value.

Data grain: The level of detail represented by one row.

Fact table: A table containing measurable business events or transactions.

Dimension table: A table containing descriptive attributes used to analyse facts.

Append: An operation that stacks rows from compatible tables.

Merge: An operation that joins columns from related queries using matching fields.

Primary key: A field or combination of fields intended to identify each row uniquely.

Foreign key: A field that refers to a key in another table.

Query folding: Translation of Power Query operations into processing performed by the source system.

Staging query: A query used to connect to and initially standardise source data.

Exception table: A table containing records requiring investigation or correction.

Calculated column: A column computed for each row and stored in the model.

Measure: A DAX expression evaluated dynamically in filter context.

Filter context: The set of filters affecting a DAX calculation.

Data lineage: The traceable relationship between source data, transformations, and outputs.

Review Questions

Short Answer Questions

1. What is the main purpose of data profiling?
2. How does an append differ from a merge in Power Query?
3. Why must product keys be unique on the lookup side of a merge?
4. Distinguish a null value from a numeric zero.
5. Give one reason to implement a calculation as a DAX measure rather than a Power Query custom column.

Long Answer Questions

1. Explain a systematic workflow for preparing a raw sales dataset for modelling in Power BI.
2. Discuss how incorrect data types can affect sorting, aggregation, relationships, and report interpretation.
3. Compare left outer, inner, full outer, and anti joins, giving an appropriate business use for each.
4. Explain query folding and identify practical steps that may improve refresh performance.
5. Discuss the governance and privacy responsibilities associated with preparing business data.

Analytical / Application Questions

1. A product merge increases sales rows by 8 percent. Analyse the likely causes and describe the checks you would perform before correcting the model.
2. A monthly file contains missing quantities marked N/A. Propose a preparation and exception-handling strategy, explaining why replacing every value with zero may be unsafe.
3. A manager requests a “profit margin by region” calculation that changes when slicers are used. Decide which parts belong in Power Query and which belong in DAX, and justify your design.

Multiple Choice Questions with Answers

Question 1

Which operation stacks rows from tables with compatible structures?

A. Merge

B. Append

C. Transpose

D. Group By

Correct Answer: B. Append

Question 2

Why is an explicit data type important for a date column?

A. It guarantees that every date is accurate

B. It prevents all missing values

C. It supports correct date sorting and time-based operations

D. It automatically creates a date dimension

Correct Answer: C. It supports correct date sorting and time-based operations

Question 3

What does a null generally represent?

A. A known value of zero

B. An unknown or missing value

C. A duplicate key

D. A text value containing one space

Correct Answer: B. An unknown or missing value

Question 4

Which join is usually appropriate when all sales rows must be retained while adding optional product attributes?

A. Left outer join

B. Inner join

C. Right anti join

D. Full anti join

Correct Answer: A. Left outer join

Question 5

What is a major risk of merging a fact table with a lookup containing duplicate keys?

A. Dates become text automatically

B. Rows may be multiplied and totals inflated

C. All unmatched rows are deleted

D. The model cannot contain measures

Correct Answer: B. Rows may be multiplied and totals inflated

Question 6

Which requirement is most suitable for a DAX measure?

A. Trimming spaces from product codes

B. Removing test rows during refresh

C. Appending monthly files

D. Calculating margin that responds to report filters

Correct Answer: D. Calculating margin that responds to report filters

Question 7

Which technique is most useful for identifying records in a primary query with no matching lookup key?

A. Anti join

B. Transpose

C. Fill Down

D. Pivot Column

Correct Answer: A. Anti join

Question 8

What is query folding?

A. Compressing a report page

B. Converting DAX into M

C. Translating transformations for execution by the source system

D. Combining all model tables into one table

Correct Answer: C. Translating transformations for execution by the source system

Question 9

Why should missing quantities not automatically be replaced with zero?

A. Zero is not a numeric value

B. Missing may mean unknown rather than no quantity

C. Power Query cannot replace values

D. DAX requires all quantities to be text

Correct Answer: B. Missing may mean unknown rather than no quantity

Question 10

Which statement best distinguishes a calculated column from a measure?

A. A calculated column is evaluated per row and stored; a measure is evaluated in context

B. A measure is always calculated before data is loaded

C. A calculated column can never use source fields

D. Measures cannot respond to slicers

Correct Answer: A. A calculated column is evaluated per row and stored; a measure is evaluated in context