Business Intelligence Data Preparation Foundations

Introduction

Business intelligence (BI) converts data into information that supports organisational decisions. Although dashboards and visualisations are highly visible outputs, their reliability depends largely on activities that occur before analysis: locating data, understanding its meaning, combining sources, correcting quality problems, and shaping it for reporting. These activities are collectively known as data preparation.

In a Power BI environment, Power Query is the primary preparation tool. It enables analysts to connect to files, databases, web services, and other sources; inspect their contents; apply repeatable transformations; and load the resulting tables into a data model. Power Query is therefore more than a convenience for cleaning spreadsheets. It is an implementation environment for a disciplined portion of the BI lifecycle.

This chapter establishes the conceptual foundations needed to use Power Query responsibly. It explains the BI lifecycle, ETL and ELT, data types and structures, schemas, metadata, granularity, and the distinction between data and decision-ready information.

Learning objectives

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

• describe the principal stages of the BI lifecycle;
• distinguish ETL from ELT and identify suitable use cases for each;
• differentiate structured, semi-structured, and unstructured data;
• explain schemas, metadata, keys, relationships, and data types;
• determine the granularity of a dataset and recognise the consequences of changing it;
• explain how Power Query supports repeatable data preparation;
• identify common quality, governance, privacy, and security risks; and
• evaluate whether a prepared dataset is suitable for a business decision.

1. The business intelligence lifecycle

BI is best understood as a lifecycle rather than a single report-building activity. A typical lifecycle contains the following connected stages:

1. Business need definition. A decision, performance question, or operational problem is expressed clearly. For example, a sales manager may ask why regional margins have declined.
2. Data discovery and acquisition. Relevant internal and external sources are located, accessed, and documented.
3. Preparation and integration. Data is profiled, cleaned, transformed, combined, and checked.
4. Modelling. Tables, relationships, measures, and business definitions are organised into a model.
5. Analysis and communication. Reports, dashboards, alerts, and explanations are produced for intended users.
6. Decision and action. Users interpret evidence and take an operational, tactical, or strategic action.
7. Evaluation and improvement. Results, data quality, user behaviour, and changing requirements are reviewed. The cycle then begins again.

These stages are not strictly linear. A missing field may send an analyst back from modelling to source discovery; an ambiguous business definition may require a return to requirements analysis. Effective BI therefore involves feedback and iteration.

Figure: The BI lifecycle is iterative; review and action inform the next business question.

A technically correct report can still be poor BI if it answers the wrong question, uses an unsuitable population, or presents measures that users interpret differently. Data preparation must therefore be guided by business meaning, not merely by the desire to remove visible errors.

2. From raw data to decision-ready information

Data consists of recorded observations, such as an invoice number, transaction date, quantity, or customer code. Information is data interpreted in context. A list of monthly revenue values becomes information when the period, currency, business unit, comparison basis, and decision purpose are known.

A useful preparation chain is:

raw data → validated data → integrated data → model-ready data → decision-ready information

Preparation does not create truth automatically. It improves usability and consistency while preserving a clear connection to source evidence. Analysts should be able to explain where a value came from, what transformations were applied, and which assumptions affect its interpretation.

Common preparation activities include:

• removing duplicate records when duplicates are erroneous;
• standardising names, dates, currencies, and units;
• handling missing, invalid, or out-of-range values;
• splitting or combining columns;
• joining related tables using appropriate keys;
• appending tables with compatible structures;
• filtering irrelevant records;
• pivoting or unpivoting data to match analytical needs; and
• documenting definitions and transformation decisions.

A key professional principle is do not silently change meaning. Replacing a missing sales value with zero, for example, is not a neutral cleaning operation. A missing value may indicate that a transaction was not reported, whereas zero may mean that no sale occurred. The correct treatment depends on the business process and must be justified.

3. ETL and ELT processes

3.1 ETL

ETL means extract, transform, load. Data is extracted from source systems, transformed in a separate preparation environment, and then loaded into a target data warehouse or analytical store.

ETL is useful when:

• the target system should receive only validated and approved data;
• transformation logic is centrally controlled;
• source data requires substantial cleansing before storage; or
• the target has limited transformation capability.

3.2 ELT

ELT means extract, load, transform. Data is extracted and loaded into a target platform, after which transformations are performed using the target platform's processing capacity. ELT is common in cloud data platforms that can store large volumes economically and execute transformations at scale.

ELT may preserve raw data for future analysis, but it increases the importance of access controls, storage management, lineage, and careful separation between raw and curated layers.

Power Query can participate in an ETL-like workflow when it extracts data, transforms it, and loads the result into a Power BI model. In broader architectures, it may also be one transformation layer within an ELT or lakehouse pattern. The labels describe process order; they do not by themselves determine quality.

3.3 Query folding

When Power Query connects to a source capable of executing transformations, it may translate compatible steps into the source system's query language. This behaviour is known as query folding. Filtering rows or selecting columns early may therefore be executed by the database rather than locally, reducing the amount of data transferred.

Query folding depends on the connector, source, transformation, and sequence of steps. It should be checked rather than assumed. A transformation that cannot be translated may cause later steps to execute in the Power Query engine. This affects performance, but it does not alter the conceptual need for correct definitions and validation.

4. Data structures and data types

4.1 Structured data

Structured data follows a defined format, usually rows and columns with known fields. Relational database tables, properly designed spreadsheets, and point-of-sale transaction tables are examples. Structured data is relatively easy to filter, join, validate, and aggregate because its fields and relationships are explicit.

4.2 Semi-structured data

Semi-structured data has organisational markers but does not necessarily use a fixed relational table. JSON, XML, and many application programming interface (API) responses use nested objects, arrays, and name-value pairs. Power Query can expand records and lists, but the analyst must decide how nested structures should become tables and whether repeated elements represent separate business events.

4.3 Unstructured data

Unstructured data does not follow a consistent tabular schema. Examples include email text, images, audio, video, and free-form documents. It may require optical character recognition, natural language processing, computer vision, or manual classification before it can be analysed alongside structured data. Importing an unstructured source into a tool does not automatically make its meaning reliable.

A practical distinction is that structure concerns how data is organised, while quality concerns whether it is accurate, complete, consistent, timely, and fit for purpose. Structured data can be poor quality, and unstructured data can contain valuable evidence.

5. Schemas, keys, relationships, and metadata

A schema describes the organisation of data. In a table, it commonly includes column names, data types, constraints, and relationships to other tables. A sales schema might contain a fact table with transaction-level amounts and dimension tables for products, customers, dates, and locations.

A primary key uniquely identifies a row in a table. A foreign key stores a value that refers to a key in another table. Relationships enable analysis across tables, but they are valid only when the key meanings and levels of detail are compatible. If a customer table contains one row per customer but a transaction table contains many rows per customer, the customer identifier can usually relate the dimension to the fact table in a one-to-many pattern.

A star schema generally places measurable events in a central fact table and descriptive entities in surrounding dimension tables. It is often preferable for BI because it makes filtering paths and business grain explicit. A well-designed model is not a substitute for preparation: duplicate keys, inconsistent codes, and unclear dates can still produce incorrect results.

Metadata is data about data. It may describe:

• field name and business definition;
• data type, format, and unit of measure;
• source system and extraction time;
• owner, steward, and sensitivity classification;
• valid values and quality rules;
• transformation history and lineage; and
• refresh frequency and retention requirements.

Technical metadata helps a system process data. Business metadata helps people interpret it. For example, a field named Amount is incomplete metadata unless users know whether it means gross sales, net sales, tax-inclusive amount, or a particular currency.

6. Granularity: the level of detail

Granularity, or grain, is the level at which each row represents an event or observation. A sales table may have one row per invoice, one row per invoice line, one row per customer per month, or one row per region per quarter. These are different datasets even if they contain similarly named columns.

Before transforming a table, complete the sentence:

“One row represents …”

If the sentence cannot be completed unambiguously, the table is not yet understood well enough for reliable modelling.

Suppose an invoice contains three product lines. In an invoice-line table, the invoice total may appear on all three rows. Summing that repeated invoice total would overstate revenue. The appropriate measure may instead be the sum of line amounts, or the invoice total after deduplicating to invoice grain. This is a grain problem, not merely a formula problem.

Granularity also affects joins. Joining two tables at different grains can multiply rows. For example, joining monthly customer targets to daily transactions by customer without including month may repeat targets across every day. Keys must therefore identify the intended relationship at the intended grain.

Aggregation is a deliberate change of grain. If daily sales are grouped by month and region, the resulting row represents one region-month combination. Detail that is not included in the grouping is no longer directly available in that table. Analysts should retain a detailed layer when future questions may require it.

7. Power Query as a preparation environment

Power Query uses a sequence of named transformation steps. A query normally follows this pattern:

1. connect to a source;
2. inspect source structure and profile values;
3. set appropriate data types;
4. remove irrelevant columns and rows;
5. standardise and validate values;
6. reshape, combine, or enrich tables;
7. check results and dependencies; and
8. load the output to the model or another destination.

The sequence is recorded as instructions rather than as a one-time manual edit. When the source is refreshed, the steps can be applied again. This repeatability improves consistency and auditability, provided that the source structure and assumptions remain stable.

Power Query supports important operations:

• Filter: retain rows meeting a condition.
• Choose or remove columns: reduce unnecessary data and protect sensitive fields.
• Change type: interpret text, whole number, decimal, date, time, Boolean, or other values appropriately.
• Replace values: standardise known codes or labels, with care to avoid changing legitimate distinctions.
• Split and merge columns: separate composite fields or construct a controlled key.
• Fill and handle errors: address missing values or conversion failures according to business rules.
• Merge queries: join related tables using matching columns.
• Append queries: stack rows from sources with compatible column meanings.
• Pivot and unpivot: change the orientation of attributes and measures.
• Group by: produce an intentional aggregate at a specified grain.

A disciplined transformation order

There is no universal order for every query, but a useful pattern is to remove unnecessary columns and rows early, assign data types before type-dependent operations, standardise keys before joins, and aggregate only after verifying grain. Early reduction can improve performance, while explicit, named steps make review easier.

Avoid excessive manual intervention. A transformation that depends on selecting a changing column position may fail when a source adds a column. Prefer stable column names, documented rules, and parameterised paths where appropriate. The objective is not simply to make today's preview look correct; it is to create a refreshable process.

8. Data quality and validation

Data quality is multidimensional. Common dimensions include:

• accuracy: values represent the real-world event;
• completeness: required values are present;
• consistency: the same concept uses compatible values across sources;
• validity: values conform to rules and data types;
• timeliness: data is available and current enough for its use; and
• uniqueness: duplicate records do not distort the intended grain.

Validation should occur at several levels. At field level, check types, ranges, formats, and permitted values. At row level, check whether required combinations are present. At table level, check row counts, duplicate keys, and grain. At model level, check relationship cardinality, totals, and reconciliation to a trusted source.

A reconciliation is a comparison between an output and an independent expected value. For example, the prepared total for a month may be compared with the finance system's approved total. A difference does not automatically prove that the query is wrong; it signals that definitions, timing, exclusions, or source versions must be investigated.

9. Illustrative case: preparing regional sales data

This is a fictional illustrative case.

Background

Northstar Office Supplies receives monthly sales workbooks from four regions. Each workbook contains invoice lines, but column names, date formats, region labels, and currency conventions differ. Management wants a Power BI report showing sales and gross margin by region, product category, and month.

Problem

The initial report shows inconsistent totals. One workbook includes cancelled invoices, another uses text dates in day-month-year format, and a third repeats invoice totals on every product line. Region names include “North”, “NORTH ”, and “N.” A direct append would create inconsistent categories and inflated totals.

Analysis

The analyst first defines the grain: one row should represent one invoice line. The line amount is the additive sales value, while the invoice total is retained only for reconciliation. A source inventory records workbook name, reporting period, currency, and responsible owner.

The analyst then profiles each source and identifies transformation rules:

• standardise column names to a common dictionary;
• convert dates using the known regional culture rather than guessing;
• trim whitespace and map approved region codes;
• exclude cancelled invoices according to the agreed status rule;
• convert currencies using the approved monthly exchange-rate table;
• append the four regional tables after aligning their schemas;
• merge product and calendar dimensions; and
• create validation checks for duplicate invoice-line identifiers and monthly totals.

Approach and solution

Power Query queries are separated into staging queries and a final fact query. Staging queries preserve source-specific logic, while the final query applies common rules. A reference table maps source labels to standard region names. The final fact table contains invoice-line identifiers, dates, product keys, region keys, quantity, net sales, cost, and gross margin inputs.

The analyst does not replace missing cost with zero. Instead, missing cost is flagged for investigation because treating it as zero would falsely increase margin. A quality report displays row counts, missing values, invalid keys, and reconciliation differences before the dataset is published.

Outcome

The report now provides consistent regional comparisons, and users can trace each result to a source period and transformation step. Management discovers that the apparent decline in one region was partly caused by cancelled invoices being included in the previous report. The remaining decline is then investigated as a genuine business issue.

Learning points

The case demonstrates that preparation is a combination of technical transformation and business interpretation. Grain must be defined before aggregation, source differences must be documented, and quality exceptions should be visible rather than silently overwritten.

10. Governance, ethics, and security

Data preparation can create ethical and regulatory risks. Analysts should collect and process only data needed for a legitimate purpose, restrict access according to role, and protect personal or commercially sensitive fields. Names, addresses, employee identifiers, and customer contact details may require masking, minimisation, or exclusion from a reporting model.

Important governance practices include:

• establish ownership and stewardship for important datasets;
• document definitions, sources, transformations, and refresh schedules;
• preserve lineage from report output to source;
• apply least-privilege access and secure credentials;
• distinguish development, testing, and production data;
• retain data only as long as justified; and
• test whether transformations introduce unfair or misleading outcomes.

Automation and AI-assisted preparation can suggest data types, detect anomalies, classify text, or generate transformation code. Such assistance is useful but not authoritative. Generated logic must be reviewed for privacy, correctness, bias, reproducibility, and hidden assumptions. An analyst remains accountable for publishing a decision-support dataset.

11. Contemporary practice and best practices

Modern BI environments increasingly combine spreadsheets, operational databases, cloud storage, APIs, event streams, and semantic models. Dataflows, lakehouses, and medallion-style architectures separate raw, cleaned, and curated layers. These approaches can improve reuse, but they also increase the need for clear ownership and consistent definitions.

Recommended practices for beginning Power Query work are:

1. start with the business question and define required measures;
2. inventory sources before transforming them;
3. write down the grain of every important table;
4. preserve raw or staged data where governance permits;
5. assign data types deliberately and early;
6. standardise keys before joining;
7. avoid replacing missing values without a business rule;
8. remove unnecessary sensitive columns;
9. name steps clearly and keep transformations understandable;
10. validate row counts, keys, totals, and refresh behaviour; and
11. document assumptions so another analyst can reproduce the result.

Chapter Summary

Business intelligence is an iterative lifecycle that begins with a business question and continues through data acquisition, preparation, modelling, communication, action, and review. Data preparation is central because reports inherit many of the strengths and weaknesses of their sources.

ETL transforms data before loading it into a target, whereas ELT loads data before transforming it. Structured, semi-structured, and unstructured data require different preparation strategies. Schemas describe organisation; metadata explains meaning, origin, quality, and governance. Keys and relationships connect tables, while granularity defines what each row represents. Failure to understand grain can produce duplicated values, incorrect joins, and misleading aggregates.

Power Query provides a repeatable environment for connecting to sources, profiling, transforming, combining, and validating data. Its usefulness depends on disciplined business definitions, explicit assumptions, quality checks, and responsible governance. Decision-ready information is not merely clean-looking data; it is data whose meaning, lineage, limitations, and fitness for purpose are understood.

Key Terms

Business intelligence: The use of data, analysis, and communication to support organisational decisions.

Data preparation: The process of profiling, cleaning, transforming, integrating, and validating data for analysis.

ETL: Extract, transform, load; a process that transforms data before loading it into a target.

ELT: Extract, load, transform; a process that transforms data after loading it into a target platform.

Structured data: Data organised in a defined schema, commonly rows and columns.

Semi-structured data: Data with organisational markers such as nested records or tags but without a fixed relational structure.

Unstructured data: Data without a consistent tabular schema, such as text, images, audio, or video.

Schema: The structural description of tables, fields, types, constraints, and relationships.

Metadata: Data describing the meaning, origin, structure, quality, ownership, or use of data.

Primary key: A field or combination of fields that uniquely identifies a row.

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

Relationship: A defined association between tables through related keys.

Granularity: The level of detail represented by one row in a dataset.

Fact table: A table containing measurable business events, usually at a declared grain.

Dimension table: A table containing descriptive attributes used to filter or group facts.

Query folding: Translation of compatible Power Query steps into operations executed by the source system.

Data lineage: The documented path from a data source through transformations to an output.

Data quality: The degree to which data is accurate, complete, consistent, valid, timely, and unique for its purpose.

Decision-ready information: Validated and contextualised information suitable for a defined decision or action.

Review Questions

Short Answer Questions

1. What are the main stages of the BI lifecycle?
2. How does ETL differ from ELT?
3. Define data granularity and give one example of a table grain.
4. What is metadata, and why is it important?
5. What is the purpose of query folding in Power Query?

Long Answer Questions

1. Explain why data preparation should be guided by business meaning rather than by technical cleaning alone.
2. Compare structured, semi-structured, and unstructured data, including the preparation challenges associated with each.
3. Explain how primary keys, foreign keys, schemas, and relationships support a BI model.
4. Describe a disciplined Power Query workflow from source connection to publication.
5. Discuss the principal data-quality, governance, privacy, and security risks in BI preparation.

Analytical / Application Questions

1. A transaction table contains one row per invoice line, but the invoice total is repeated on every line. Explain how summing the invoice-total column could distort results and propose a preparation approach.
2. Two regional tables use different date formats and different labels for the same regions. Design a sequence of Power Query steps that would make them suitable for appending and reporting.
3. A monthly target table is joined to daily sales by customer only, causing targets to appear repeatedly. Diagnose the grain and relationship problem and recommend a correction.

Multiple Choice Questions with Answers

Question 1

Which activity belongs primarily to the data preparation stage of the BI lifecycle?

A. Defining an organisation’s strategic mission

B. Standardising date formats and validating keys

C. Presenting a dashboard to executives

D. Approving an operational budget

Correct Answer: B. Standardising date formats and validating keys

Question 2

What is the defining order of an ETL process?

A. Extract, transform, load

B. Evaluate, translate, link

C. Extract, test, launch

D. Enter, transform, list

Correct Answer: A. Extract, transform, load

Question 3

Which example is semi-structured data?

A. A relational sales table with fixed columns

B. A scanned photograph

C. A JSON response containing nested records

D. A printed annual report

Correct Answer: C. A JSON response containing nested records

Question 4

What does granularity describe?

A. The colour scheme of a report

B. The level of detail represented by one row

C. The number of reports in a workspace

D. The speed of a data refresh

Correct Answer: B. The level of detail represented by one row

Question 5

Which statement best describes metadata?

A. It is always a duplicate copy of source data

B. It describes data’s meaning, origin, structure, or governance

C. It is data that has been deleted

D. It is a visual filter in a dashboard

Correct Answer: B. It describes data’s meaning, origin, structure, or governance

Question 6

What is a primary key used for?

A. To format a date column

B. To uniquely identify a row

C. To encrypt a database

D. To calculate an average

Correct Answer: B. To uniquely identify a row

Question 7

Which Power Query operation combines rows from tables with compatible structures?

A. Append

B. Merge

C. Transpose

D. Profile

Correct Answer: A. Append

Question 8

Why can summing a repeated invoice total create an incorrect result?

A. The invoice number becomes a date

B. The same invoice total is counted once for every invoice line

C. Power Query cannot read numerical values

D. A foreign key always removes values

Correct Answer: B. The same invoice total is counted once for every invoice line

Question 9

What is query folding?

A. Compressing a report page

B. Translating compatible transformation steps for execution by the source

C. Combining all tables into one column

D. Deleting source metadata

Correct Answer: B. Translating compatible transformation steps for execution by the source

Question 10

Which action is most appropriate when cost is missing and margin depends on cost?

A. Automatically replace cost with zero

B. Ignore the missing value without documentation

C. Flag the record for investigation according to a business rule

D. Duplicate the previous cost value for every record

Correct Answer: C. Flag the record for investigation according to a business rule