Data Acquisition, Quality and Transformation

Introduction

Business intelligence is only as reliable as the data on which it is built. A visually attractive Power BI dashboard can still produce poor decisions if its source data is incomplete, duplicated, inconsistently defined, or transformed without control. Data acquisition and transformation therefore constitute a core professional capability, not merely a preliminary technical task.

Power Query is the principal data-preparation environment used in Power BI. It supports connections to files, databases, web services and organisational systems; inspection and profiling of data; transformation through a sequence of documented steps; and loading of prepared data into a semantic model. Although Power Query is commonly associated with extract, transform and load (ETL), it can also support extract, load and transform (ELT), particularly when transformations are delegated to a source database through query folding.

This chapter explains how to acquire, profile, clean, combine and reshape data systematically. It also examines data-quality dimensions, lineage, repeatability, governance and practical limitations.

Learning Objectives

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

• distinguish common data sources and connection approaches in Power BI;
• profile data using column quality, distribution and profile information;
• identify and address missing values, duplicates, errors and inconsistent types;
• combine tables through append and merge operations;
• reshape data using pivot, unpivot, split, aggregation and grouping;
• explain query steps, dependencies, lineage and query folding;
• design repeatable ETL/ELT processes with validation and documentation;
• evaluate the professional, ethical and governance implications of data preparation.

1. The Data-Preparation Problem

A source system is designed for operational work, not necessarily for analysis. An order-entry system may contain one row per transaction, while a dashboard requires monthly sales by region and product category. A spreadsheet may contain manually entered dates, merged cells, subtotals and inconsistent spellings. A database may contain valid but highly normalised tables that must be related before analysis.

Data preparation converts source data into a form that is suitable for reliable analysis. It normally involves five related activities:

1. Acquisition – connecting to and retrieving data.
2. Inspection – understanding structure, values, quality and meaning.
3. Transformation – changing structure, types and representations.
4. Validation – testing whether the prepared data satisfies requirements.
5. Documentation and loading – recording lineage and delivering data to the model.

A useful distinction is between a source defect and a transformation requirement. A blank customer telephone number may be a source defect if the field is expected to be complete. Conversely, converting a timestamp into a calendar date is a transformation requirement because the analytical question concerns dates rather than times. Good analysts do not “clean” data indiscriminately; they alter it according to documented business and technical rules.

2. Acquiring Data with Power Query

Power BI provides connectors for sources such as Excel workbooks, CSV files, SQL Server, SharePoint, online services, OData feeds and web APIs. The choice of connector affects authentication, refresh behaviour, performance and available metadata.

2.1 Connection principles

When acquiring data, consider:

• Authority: Is the source approved and trustworthy?
• Grain: What does one row represent?
• Scope: What period, entities and measures are included?
• Stability: Will the location, schema or file name change?
• Refresh: Can Power BI access the source when scheduled refresh occurs?
• Security: Are credentials, personal data and access permissions controlled?
• Performance: Can the source filter or aggregate data before transfer?

For files, a folder connector is often preferable to importing files one at a time when a recurring file-drop process is used. However, the files must have a controlled structure. Combining unrelated files from the same folder can silently introduce errors.

2.2 ETL and ELT

In ETL, data is extracted, transformed in a preparation layer and then loaded into the analytical destination. In ELT, data is extracted and loaded first, with transformations performed in the destination or a computationally capable source platform.

Power Query can implement either pattern. If a transformation is translated back to the source database, it may be executed there through query folding. Folding can reduce the volume of data transferred and improve refresh performance. Not every transformation folds, and folding behaviour depends on the connector, source system and sequence of steps. Analysts should therefore inspect performance rather than assume that folding occurs.

3. Understanding Power Query Structure

Power Query records transformations as an ordered sequence of Applied Steps. Each step takes the result of the previous step as its input. The underlying language, Power Query M, is functional: expressions produce values, and queries can refer to earlier steps.

A typical query may contain the following sequence:

1. connect to a source;
2. select a table or file;
3. promote the first row to headers;
4. remove irrelevant columns;
5. set data types;
6. filter invalid records;
7. replace or investigate missing values;
8. merge with a lookup table;
9. rename columns using business terminology;
10. load the result.

The sequence matters. Removing unnecessary columns early can reduce processing. Setting types before calculations ensures that arithmetic and date operations behave correctly. Renaming columns early improves readability, but names should remain stable enough for later steps and downstream reports.

A query should have one clear purpose. For example, a staging query may standardise raw sales files, while a dimension query may produce a distinct list of customers. Duplicating lengthy transformation logic across many queries makes maintenance difficult; referencing a common staging query is usually more sustainable.

4. Profiling and Data Quality

Data profiling is the systematic examination of data content, structure and quality before extensive transformation. Power Query provides column quality, column distribution and column profile views. These tools can reveal nulls, errors, distinct-value patterns, unusual minimum or maximum values and unexpected frequency distributions.

Important dimensions of data quality include:

A basic completeness rate can be expressed as:

The formula assumes that each required field occurrence has equal importance. If missing values have different business consequences, a weighted measure or separate critical-field measures may be more appropriate. For example, a missing customer name and a missing transaction identifier should not necessarily receive the same treatment.

4.1 Interpreting profiling results

A null count is not automatically evidence of poor quality. A middle name may legitimately be absent, whereas an invoice date usually cannot be absent for a financial transaction. Profiling must therefore be interpreted using data definitions and business rules.

Profile at both the whole-table and column level. A column may appear complete in a sample but contain nulls later in the dataset. Power Query profiling options may operate over a preview rather than the entire dataset, depending on the selected setting. For important controls, validate against the complete query result or source system.

5. Data Types and Standardisation

Data types determine how values are stored, compared, displayed and calculated. Common types include text, whole number, decimal number, fixed decimal number, date, time, date/time, duration, logical and binary.

Incorrect types cause subtle failures. A numeric amount imported as text cannot be reliably summed. A date imported as text may be interpreted differently under different regional settings. A product code such as “00124” should usually remain text because leading zeroes are meaningful.

Recommended practices include:

• assign types explicitly rather than relying on automatic detection;
• distinguish identifiers from measures;
• use a suitable locale when converting dates and numbers;
• retain precision appropriate to the business process;
• avoid rounding during staging unless the rule is documented;
• check whether null, empty string and whitespace have different meanings.

For example, the text values "2025-03-04" and "04/03/2025" may represent different dates depending on locale. A transformation should specify the intended culture and should be tested with known examples. Currency values also require care: binary floating-point representations can create small computational differences, while fixed-decimal types are often more appropriate for financial amounts.

6. Missing Values, Errors and Outliers

6.1 Missing values

Missing values should be classified before treatment. Common categories are:

• Not applicable: the attribute does not apply;
• Not collected: the process failed to capture it;
• Unknown: the value exists but is not known;
• Withheld: the subject or organisation intentionally did not provide it;
• Not yet available: the value is expected later.

Possible treatments include retaining null, replacing with a justified default, filling from another source, carrying a previous value forward in time-series data, or excluding the record from a particular calculation. A replacement such as zero is safe only when zero genuinely means “none.” Replacing an unknown revenue value with zero would understate performance.

6.2 Errors

Power Query errors can arise from invalid type conversions, malformed dates, division by zero or failed source operations. Errors should be isolated and investigated rather than removed automatically. A controlled process may add an error flag, preserve the original value, and route invalid records to an exception table.

6.3 Outliers

An outlier is an unusually extreme observation, but unusual does not mean incorrect. A very large order may be a legitimate wholesale transaction. Outlier treatment should therefore use domain rules, source verification and analytical purpose. Deleting extreme values without documentation can distort results and conceal important events.

7. Removing Duplicates and Protecting Grain

A duplicate is not simply a row that looks similar to another row. It is a repeated record relative to a defined business key and intended grain. If the grain is “one row per order line,” repeated product lines may be legitimate; if the grain is “one row per order,” they may represent duplication or a different table design.

Before removing duplicates, identify:

• the intended grain;
• the candidate key or combination of columns;
• whether repeated records are exact duplicates or conflicting versions;
• which record should be retained if values differ;
• whether duplicates indicate an upstream process problem.

For example, removing duplicates based only on customer name can incorrectly merge two people with the same name. A stable customer identifier is preferable. If a table contains multiple versions of a record, a documented rule such as retaining the latest valid update may be required, using a timestamp and a tie-breaking rule.

8. Combining Data: Append and Merge

Two operations are central to combining tables.

8.1 Append

Append places rows from one table beneath rows from another. It is appropriate when tables have the same conceptual grain and broadly corresponding columns, such as monthly sales files. Matching is based on column names, not merely column position; missing columns may produce nulls.

Before appending, standardise column names and data types. Confirm that the source periods do not overlap and consider adding a source-file or period column for traceability.

8.2 Merge

Merge joins columns from one table to another using one or more matching keys. It is similar conceptually to a relational join. Common join types include:

A left outer merge is often used to enrich fact records with descriptive attributes. Anti-joins are valuable quality checks: unmatched product keys may indicate missing master data or invalid codes.

A merge can multiply rows if the lookup key is not unique. If one sales row matches three customer rows, the sales amount may be repeated three times. Always test key uniqueness before merging and validate row counts and totals afterwards.

9. Reshaping Data for Analysis

Operational data is often stored in a format convenient for entry but unsuitable for analytical modelling.

• Pivot converts distinct values in a column into separate columns, often with an aggregation.
• Unpivot converts multiple measure columns into attribute-value rows.
• Group By creates summaries such as totals, counts or averages.
• Split Column separates a compound field using a delimiter, position or character transition.
• Transpose exchanges rows and columns, but should be used cautiously because it can weaken column semantics.

Consider a table with columns Product, Jan, Feb and Mar. Unpivoting the month columns produces Product, Month and Sales, a structure that supports a standard date relationship and scalable reporting. By contrast, leaving months as columns makes future months require structural changes and complicates time intelligence.

Reshaping must preserve meaning. A wide table containing different measures should not be unpivoted into a single value column unless the measure name is also retained. Otherwise, sales, cost and quantity may become indistinguishable.

10. Lineage, Documentation and Repeatability

Data lineage describes where data originated, how it changed and where it is consumed. In Power BI, lineage can be documented through query names, descriptions, source references, applied steps, parameters, dataflows, model relationships and deployment documentation.

A professional transformation process should record:

• source system, location and owner;
• extraction date or refresh time;
• business definition and grain of each output table;
• transformation rationale;
• data-type and standardisation rules;
• quality checks and exception handling;
• credentials and sensitivity classification;
• downstream reports, measures and consumers;
• responsible person and change history.

Parameters can make queries reusable. For example, a folder path, server name, reporting period or environment can be parameterised rather than embedded in code. This supports development, testing and production environments and reduces manual editing.

Repeatability means that the same inputs and rules produce a consistent result, subject to documented source changes. Avoid manual copy-and-paste transformations. Use stable keys, explicit types, controlled reference data and named steps. A refresh should be observable: row counts, rejected records, unmatched keys and key totals should be monitored.

11. A Practical Data-Quality Control Framework

A useful control cycle is:

1. Define: document grain, required fields, keys and acceptable ranges.
2. Profile: measure nulls, distinct values, types, duplicates and errors.
3. Transform: standardise and reshape according to approved rules.
4. Validate: test row counts, totals, relationships and exceptions.
5. Publish: load only data that meets release criteria.
6. Monitor: compare future refreshes with expected patterns.
7. Improve: correct root causes rather than repeatedly patching symptoms.

Validation examples include:

• total sales before and after transformation, allowing for documented exclusions;
• count of distinct order IDs;
• percentage of rows with valid customer and product keys;
• number of records rejected due to invalid dates;
• minimum and maximum transaction dates;
• comparison of current row counts with previous refreshes.

A control should have an owner and a response. Detecting 500 unmatched product keys is not sufficient if nobody decides whether to stop publication, publish with a warning or correct the master data.

12. Applied Case: Regional Retail Sales Dashboard

The following is an illustrative case.

Background

A retailer receives monthly CSV files from four regional offices. Each file contains transaction date, invoice number, product code, region, quantity and sales amount. Product descriptions and categories are maintained in a separate product workbook. Management wants a Power BI dashboard showing sales by month, region and category.

Problem

The files use inconsistent column names. One office records dates as DD/MM/YYYY, another includes timestamps, and a third uses text values such as N/A for quantity. Product codes sometimes contain leading zeroes. The product workbook contains two rows for one product code, and approximately 2% of transactions have no matching product.

Analysis

The required grain is one row per invoice line. Invoice number alone is therefore not a sufficient key; invoice number combined with product code and line number, where available, is more appropriate. The product code must remain text. The regional files should be appended only after their columns are renamed and types standardised.

The duplicate product-code rows indicate a master-data problem. Automatically selecting the first row could assign an arbitrary category. The unmatched transactions may represent obsolete products, spelling errors or missing master records.

Approach and solution

A staging query is created for each file format. Dates are converted using the correct locale, timestamps are reduced to dates where the analytical requirement is daily reporting, and N/A quantities are converted to null with an exception flag. Product codes are trimmed and padded only where the business specification confirms the required length.

The regional queries are appended into a single sales staging query. A product-reference query is profiled for duplicate keys. After the business owner resolves the conflicting product record, a left outer merge adds product description and category. A separate left anti-join lists unmatched product codes for investigation.

The output is reshaped into a star-schema-friendly structure: a sales fact table containing transaction measures and keys, and dimensions for date, product and region. Validation compares source and output row counts, sales totals, distinct invoice-line keys and unmatched-key counts. The dashboard is not released until the product-owner approves the exception treatment.

Outcome

The retailer obtains a refreshable process rather than a manually assembled report. Regional files can be added to the controlled folder, and the query automatically includes files that meet the agreed schema. Data-quality exceptions remain visible, and management can distinguish genuine zero sales from missing values.

Learning points

The case demonstrates that transformation decisions depend on grain, keys, locale, business meaning and governance. Query automation alone does not guarantee quality. It must be accompanied by validation, ownership and documented assumptions.

13. Limitations, Governance and Responsible Practice

Power Query is powerful but not a substitute for source-system governance. If customer addresses, product keys or transaction definitions are unreliable at source, repeated downstream cleaning increases complexity and can produce inconsistent versions of truth.

Sensitive data requires particular care. Personally identifiable information should be collected and processed only for legitimate purposes, access should follow least-privilege principles, and unnecessary fields should be removed before loading. Data retention, privacy, auditability and applicable legislation must be considered according to the organisation and jurisdiction.

Analysts should not silently alter records to make a dashboard appear complete. Imputation, exclusion and correction rules must be transparent. AI-assisted transformation features may help suggest types, detect patterns or generate code, but suggestions require human review. Confidential data should not be submitted to unauthorised tools, and generated transformations must be tested for semantic and privacy errors.

Contemporary data platforms increasingly use dataflows, lakehouses, metadata catalogues, automated tests and observability tools. These developments strengthen reuse and lineage, but they also increase the need for clear ownership and well-defined business terms. A technically modern pipeline can still fail if its measures are ambiguous or its quality thresholds are unknown.

Best-Practice Checklist

Before publishing a prepared dataset, confirm that:

• the source, owner, refresh method and sensitivity are documented;
• the grain and key of every table are explicit;
• data types and locale assumptions are deliberate;
• missing values, errors and duplicates have defined treatments;
• append and merge operations have been validated;
• query folding and refresh performance have been considered;
• row counts, totals, key integrity and exception counts are tested;
• transformations are repeatable and understandable;
• lineage and downstream dependencies are recorded;
• privacy, access and retention requirements are satisfied.

Chapter Summary

Data acquisition and transformation form the foundation of trustworthy Power BI analytics. Power Query connects to diverse sources and records transformations as an ordered, inspectable process. Effective preparation begins with profiling and an understanding of grain, keys and business meaning. Data quality should be considered through completeness, validity, accuracy, consistency, uniqueness, timeliness and integrity.

Reliable solutions explicitly manage data types, missing values, errors, duplicates and outliers. Append combines rows from compatible tables, whereas merge adds columns through keys and can create duplication when keys are not unique. Pivot, unpivot, grouping and splitting reshape data for analytical use. Query folding can improve performance by delegating suitable transformations to the source.

Professional data preparation also requires lineage, documentation, validation, exception handling, privacy protection and responsible use of automation. The objective is not simply to produce a dataset that loads successfully, but to create a refreshable and defensible data pipeline whose results users can understand and trust.

Key Terms

Append: Combining tables by placing rows from one table below another.

Data lineage: A record of data origins, transformations and downstream uses.

Data profiling: Examination of data structure, values and quality characteristics.

Data quality: The extent to which data is fit for its intended purpose.

ELT: Extract, load and transform; transformation occurs after loading into a target platform.

ETL: Extract, transform and load; transformation occurs before loading into a target platform.

Grain: The level of detail represented by one row in a table.

Join key: A field or field combination used to match records between tables.

Left anti-join: A join returning rows from the first table that have no matching row in the second.

Merge: Combining columns from tables by matching keys.

Missing value: A value that is absent, unknown, inapplicable or not yet available.

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

Outlier: An unusually extreme observation relative to other observations.

Pivot: Converting values in rows into separate columns.

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

Reference query: A query that uses another query as its starting point rather than repeating source logic.

Staging query: An intermediate query used to standardise and prepare source data.

Unpivot: Converting columns into attribute-value rows.

Validation: Testing whether prepared data satisfies defined rules and expectations.

Review Questions

Short Answer Questions

1. What is the difference between an append and a merge in Power Query?
2. Why should the grain of a table be identified before removing duplicates?
3. Name four dimensions of data quality.
4. What is query folding, and why can it improve performance?
5. Why should a product code containing leading zeroes usually be typed as text?

Long Answer Questions

1. Explain a systematic process for profiling, cleaning and validating a dataset in Power Query.
2. Discuss the risks of replacing missing values with zero and identify circumstances in which zero replacement may be justified.
3. Compare ETL and ELT, including their implications for performance, governance and maintainability.
4. Explain how incorrect join-key uniqueness can affect a Power BI model and its measures.
5. Describe the documentation and lineage information required for a professional, refreshable data pipeline.

Analytical / Application Questions

1. A sales table contains 100,000 rows before transformation and 104,000 rows after a merge with a product table. Analyse possible causes and propose tests to determine whether the increase is valid.
2. A monthly file contains 3% blank customer IDs, 1% invalid dates and several repeated invoice numbers. Design a quality-control process that determines whether the report can be published.
3. A company wants to unpivot monthly columns for dashboard reporting, but some columns contain sales and others contain costs. Explain how you would reshape the data without mixing the meanings of the measures.

Multiple Choice Questions with Answers

Question 1

Which Power Query operation combines rows from tables with a similar structure?

A. Append

B. Merge

C. Pivot

D. Transpose

Correct Answer: A. Append

Question 2

Which data-quality dimension asks whether required values are present?

A. Accuracy

B. Completeness

C. Timeliness

D. Uniqueness

Correct Answer: B. Completeness

Question 3

Why should an identifier such as 00124 usually be stored as text?

A. Text always uses less storage

B. Text automatically validates the identifier

C. Leading zeroes are meaningful and should be preserved

D. Text values can always be summed

Correct Answer: C. Leading zeroes are meaningful and should be preserved

Question 4

What is the principal risk of merging a fact table with a lookup table whose key is not unique?

A. All columns become text

B. Rows may multiply and measures may be overstated

C. The source file is deleted

D. Dates are automatically removed

Correct Answer: B. Rows may multiply and measures may be overstated

Question 5

Which join returns rows in the first table that have no match in the second table?

A. Inner join

B. Full outer join

C. Left anti-join

D. Left outer join

Correct Answer: C. Left anti-join

Question 6

What does query folding mean?

A. Compressing a workbook into a ZIP file

B. Translating suitable transformations so the source performs them

C. Hiding applied steps from users

D. Converting rows into columns

Correct Answer: B. Translating suitable transformations so the source performs them

Question 7

A field is blank because the attribute does not apply to a particular customer. What is the most appropriate initial treatment?

A. Automatically replace it with zero

B. Classify it as not applicable and retain that meaning

C. Delete the entire customer

D. Copy the previous customer's value

Correct Answer: B. Classify it as not applicable and retain that meaning

Question 8

Which operation converts several month columns into a month column and a value column?

A. Unpivot

B. Merge

C. Append

D. Group By

Correct Answer: A. Unpivot

Question 9

If a completeness measure has 950 non-missing values out of 1,000 required values, what is the completeness rate?

A. 5%

B. 50%

C. 95%

D. 105%

Correct Answer: C. 95%

Question 10

Which practice best supports repeatable data preparation?

A. Manual copy-and-paste at every refresh

B. Deleting all exceptions without recording them

C. Using documented steps, parameters and validation checks

D. Relying only on automatic type detection

Correct Answer: C. Using documented steps, parameters and validation checks