Data Acquisition and Transformation with Power Query

Introduction

Business intelligence is only as reliable as the data on which it is based. In practice, data is rarely delivered as a single, clean, analysis-ready table. It may be distributed across relational databases, spreadsheets, text files, cloud applications, web services, and shared folders. Different systems may use different names, formats, time zones, data types, and definitions. Power Query provides a systematic environment for connecting to these sources, inspecting their contents, applying repeatable transformations, and loading prepared data into Power BI.

Power Query is an extract, transform, and load (ETL) technology. It can also support ELT practices when transformations are pushed back to the source system through query folding. Its transformation language, M, represents each query as a sequence of expressions and steps. This makes data preparation auditable, reusable, and easier to maintain than manual spreadsheet editing.

The central principle of this chapter is that transformation is not merely cosmetic. It establishes the meaning, structure, quality, and lineage of the data used for analysis.

Learning Objectives

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

• connect Power BI to common structured and semi-structured data sources;
• distinguish source connection, transformation, and loading activities;
• profile data and identify missing, invalid, duplicate, and inconsistent values;
• apply data-type conversion, filtering, replacement, splitting, grouping, and reshaping operations;
• merge and append queries appropriately;
• explain the role of M and read basic M expressions;
• understand query folding and its effect on performance;
• use parameters and functions to create reusable transformations;
• design data-quality controls and document transformation logic; and
• evaluate governance, privacy, and operational risks in data preparation.

1. Power Query as a Data-Preparation Environment

Power Query is available in Power BI Desktop, Excel, Power Platform products, and other Microsoft technologies. A query normally follows this conceptual pipeline:

Connect → Inspect → Transform → Validate → Load → Refresh

A connection identifies the source and obtains data or metadata. Inspection establishes what the source contains and whether it conforms to expectations. Transformation changes structure or values. Validation tests whether the resulting data is suitable for its intended use. Loading places the output into the Power BI model or another destination. Refresh repeats the process using current source data.

Power Query records transformations as Applied Steps. Each step generally refers to the result of the preceding step. This is important because a query is not a list of isolated actions: it is an ordered data-transformation program. Changing an early step may affect every subsequent step.

Common data sources

A source should be selected according to reliability, volume, refresh requirements, security, and ownership. A manually maintained workbook may be appropriate for a small controlled input, but it is a weak foundation for a high-volume, operationally critical dataset.

2. Connecting to Sources and Managing Metadata

When connecting to a source, Power BI commonly presents a Navigator window containing available tables, sheets, files, or objects. The user may choose Load for immediate import or Transform Data to open Power Query Editor. For professional work, transformation should normally be inspected before loading.

Metadata is information about the data rather than the business observations themselves. Important metadata includes:

• column names and descriptions;
• data types and formats;
• source system and table name;
• refresh frequency and time zone;
• primary and foreign keys;
• permitted values and business definitions;
• data owner and sensitivity classification; and
• transformation and quality rules.

A robust query should not depend unnecessarily on positional assumptions. For example, selecting columns by explicit name is generally safer than assuming that a required column is always the third column. Similarly, promoting the first row to headers is suitable only when the file layout is controlled and the first row truly contains field names.

For folder ingestion, Power Query can combine files using a sample file and a generated transformation function. This is efficient when all files share a stable schema. It becomes risky when files have different column names, header rows, or versions. A controlled ingestion process should validate filenames, extensions, expected columns, and duplicate files before combining them.

3. Data Profiling and Quality Assessment

Data profiling is the systematic examination of data structure and content. Power Query provides column quality, column distribution, and column profile views. These tools can reveal empty values, errors, distinct-value patterns, unexpected types, and unusual frequencies.

The principal quality dimensions are:

• Completeness: required values are present.
• Validity: values conform to permitted formats or rules.
• Accuracy: values represent the real-world object or event correctly.
• Consistency: equivalent values use compatible definitions and formats.
• Uniqueness: records or identifiers are not unintentionally duplicated.
• Timeliness: data is sufficiently current for its purpose.

A profile is evidence, not proof. A column showing 100% valid values under an inferred type does not demonstrate business accuracy. For example, the value “999999” may be a valid number technically but an invalid customer identifier in context.

Data-quality controls

A practical quality-control design contains four elements:

1. Rule: what condition must hold?
2. Detection: how is a violation identified?
3. Disposition: is the record corrected, rejected, quarantined, or retained with a warning?
4. Evidence: where is the result recorded for review?

Examples include checking that an order date is not later than its shipment date, that a product key is not blank, and that a sales amount is not negative unless returns are represented by design.

A useful pattern is to create a quality-status column rather than silently deleting records. For example, a row may receive the status “Valid,” “Missing customer,” or “Invalid date.” A separate exception query can then retain failed rows for investigation. Silent deletion reduces visibility and may distort measures.

4. Core Transformation Operations

Data types

Power Query supports types such as text, whole number, decimal number, fixed decimal number, date, time, date/time, logical, duration, binary, record, list, and table. Correct typing is essential because it affects sorting, filtering, joins, aggregation, relationships, and model storage.

A date stored as text may sort alphabetically rather than chronologically. A currency value interpreted with the wrong locale may be multiplied or divided by a factor of 10 or 100. Type conversion should therefore specify the appropriate culture when the source format is ambiguous.

Cleaning values

Common operations include trimming leading and trailing spaces, cleaning non-printing characters, changing case, replacing values, and standardising labels. For example, “United Kingdom,” “UK,” and “U.K.” may need to be mapped to one controlled value. Replacement should be based on an agreed business rule, not a guess.

Null, blank, zero, and error are not interchangeable:

• Null indicates an absent or unknown value.
• Blank text is a text value containing no characters.
• Zero is a numeric value with magnitude zero.
• Error indicates that an expression or conversion failed.

Confusing these states can produce incorrect averages, counts, and business conclusions.

Filtering and selecting columns

Filtering rows reduces the dataset to records relevant to the query. Selecting columns removes unnecessary attributes. Early reduction can improve performance and simplify downstream logic, but columns should not be removed before confirming that they are not required for joins, validation, or later calculations.

Splitting and extracting

Text fields can be split by delimiter, character position, or transition from lowercase to uppercase. A product code such as “EU-2025-014” may be divided into region, year, and sequence. Such transformations should account for malformed values and missing delimiters.

Grouping and aggregation

Grouping summarizes rows by one or more keys. Typical aggregations include sum, count, minimum, maximum, and average. Aggregation is appropriate when the required output is at a different grain from the source. Before grouping, identify the grain of the input and the grain required by the analysis. Grouping transaction rows by customer produces a customer-level result, but it may discard transaction-level detail if the original query is used directly.

Pivoting and unpivoting

A wide table places categories in columns; a long table places categories in rows. Unpivoting converts columns into attribute-value pairs and is often required for survey data, monthly spreadsheets, or repeated measure columns. Pivoting converts values in a category column into separate columns.

For example, columns such as January, February, and March may be unpivoted into Month and Amount. This creates a more flexible structure for filtering and modelling. Unpivoting is usually preferable to maintaining a growing set of month columns.

5. M Language Fundamentals

M is a functional, case-sensitive language. The graphical interface writes M code automatically, but understanding basic syntax helps users diagnose errors and create reusable logic.

A simple query expression is:

let
    Source = Excel.Workbook(File.Contents("C:\\Data\\Sales.xlsx"), null, true),
    SalesSheet = Source{[Item="Sales", Kind="Sheet"]}[Data],
    Headers = Table.PromoteHeaders(SalesSheet, [PromoteAllScalars=true]),
    Typed = Table.TransformColumnTypes(Headers, {{"OrderDate", type date}, {"Amount", Currency.Type}})
in
    Typed

The let expression defines named steps; the in expression identifies the result returned by the query. Each step is an M value. A step may be a table, list, record, text value, number, function, or another type.

A custom column can use an expression such as:

if [Amount] >= 1000 then "High" else "Standard"

A basic function has the form:

(x as number) as number => x * 1.2

This function accepts a number and returns a number. Functions are valuable when the same transformation must be applied to many files, columns, or parameter values.

M errors commonly arise from missing fields, incompatible types, invalid dates, incorrect syntax, or assumptions that fail for a new source file. Error handling should be deliberate. Replacing every error with zero may hide source problems and produce false results.

6. Merging, Appending, and Relationships

A merge combines columns from two tables using matching keys. It is conceptually similar to a relational join. The principal join types are:

A merge requires compatible key values. Before merging, examine whether each key is unique where uniqueness is expected. If a customer table contains duplicate customer keys, a merge can multiply transaction rows and inflate totals. This is a cardinality problem, not merely a technical inconvenience.

An append places rows from one table below rows from another. It is appropriate for monthly files or regional tables with the same logical columns. Columns are matched by name; missing columns produce nulls. Appending tables with incompatible meanings can create a deceptively unified dataset, so schema and business definitions must be checked first.

Merging enriches records horizontally; appending extends records vertically. Confusing the two is a common design error.

7. Query Folding and Performance

Query folding occurs when Power Query translates eligible transformation steps into operations executed by the source system, commonly SQL. Filtering a database table early may therefore reduce the volume transferred to Power BI. Folding is source- and transformation-dependent; it is not guaranteed for every connector or step.

A typical performance principle is:

Filter rows and select necessary columns early, while preserving folding where possible.

Operations that often fold include simple filters, column selection, joins, and aggregations against relational sources. Operations that may prevent folding include certain custom functions, complex row-by-row logic, native transformations unsupported by the connector, and transformations applied after data has been materialised locally.

The presence of a step that does not fold does not automatically make a query unusable. It means that later operations may execute in the Power Query engine rather than at the source. Use query diagnostics and the “View Native Query” option where available; do not infer folding solely from the appearance of the Applied Steps pane.

For large datasets, performance also depends on source indexing, network transfer, gateway capacity, model design, refresh concurrency, and source workload. Query folding is therefore an important optimisation, not a substitute for sound architecture.

8. Parameters and Reusable Transformations

A parameter is a named value that can be changed without rewriting query logic. Common parameters include:

• file path or folder location;
• server or database name;
• reporting start date;
• reporting end date;
• environment, such as development or production; and
• threshold used in a quality rule.

Parameters support deployment across environments and controlled testing. They should be documented, constrained to appropriate values, and protected when they contain sensitive connection information.

Reusable transformations can be implemented with functions. For example, a function may accept a file binary, promote headers, remove blank rows, and assign types. A folder query can then invoke that function for every file. This reduces duplication and ensures that a correction is applied consistently.

Reusable design should follow three principles:

1. define a clear input and output contract;
2. isolate source-specific assumptions; and
3. test the function with valid, empty, malformed, and changed inputs.

9. Applied Case: Regional Sales Consolidation

The following is an illustrative case.

Background

A retail organisation receives monthly sales workbooks from three regions. Each workbook contains a Sales sheet, but regional staff use different spellings for channel names and occasionally leave customer identifiers blank. Management wants a Power BI report showing sales by customer, product, region, and month.

Problem

The files have inconsistent column order, two date formats, regional currency values, and occasional duplicate transaction numbers. A manual consolidation process has produced unexplained differences between the report and the finance system.

Analysis

The required analytical grain is one valid sales transaction. The key quality risks are:

• duplicate transaction numbers within a region;
• missing or invalid dates;
• inconsistent channel labels;
• amounts interpreted using the wrong currency or locale; and
• files with unexpected schemas.

The team defines a data contract: each file must contain TransactionID, OrderDate, CustomerID, ProductID, Channel, Amount, Currency, and Region. TransactionID must be unique within a region, OrderDate must be valid, and Amount must be numeric.

Approach and solution

A folder connector is used to retrieve approved workbook files. A reusable transformation function validates the filename, selects the Sales sheet, promotes headers, trims text, applies culture-aware type conversion, and standardises channel labels. Files failing the schema test are moved to an exception output rather than silently appended.

The regional tables are appended after standardisation. A duplicate flag is generated using TransactionID and Region. Invalid rows are retained in a quality-exception query with a reason column. A customer query is merged using CustomerID, and unmatched customers are reported through a left-anti query. Currency conversion is performed only after the business-approved exchange-rate table has been joined; source amounts are not casually treated as a common currency.

Filters and column selection are applied as early as possible. The final queries are named according to their role: staging, transformation, reference, fact, dimension, and exception. Documentation records the owner, refresh schedule, assumptions, and known limitations.

Outcome

The organisation obtains a repeatable refresh process, a visible exception list, and a clearer distinction between source errors and legitimate business events. Management totals become reconcilable because duplicate and unmatched records are measured rather than hidden.

Learning points

The main improvement did not come from a single Power Query command. It came from defining grain, data contracts, quality rules, reusable transformations, and exception handling before building the report. Technical automation is most valuable when it supports a controlled business process.

10. Governance, Privacy, and Ethical Considerations

Data preparation can expose personal, confidential, or commercially sensitive information. Power Query users should apply least-privilege access, use approved connectors, and avoid copying sensitive data into uncontrolled files. Privacy principles generally require a legitimate purpose, data minimisation, appropriate security, and retention controls.

Transformations may also introduce bias. Removing records with missing values can disproportionately exclude particular regions or customer groups. Standardising categories can erase meaningful distinctions if the mapping is poorly designed. Automated cleansing should therefore be reviewable and reversible where feasible.

Data lineage should identify the source, transformations, responsible owner, refresh time, and destination. Credentials should not be embedded in visible query text or shared through unsecured documents. Gateway configuration, privacy levels, and organisational access policies should be aligned with the sensitivity of the source.

11. Contemporary Practice and Limitations

Modern Power BI environments increasingly use dataflows, lakehouses, semantic models, deployment pipelines, and automated monitoring. These capabilities can centralise reusable transformations and reduce duplicated preparation across reports. AI-assisted features may help suggest transformations or describe anomalies, but suggestions require human validation. AI does not establish business definitions, data ownership, or legal permission to use data.

Power Query also has limitations. It is not a replacement for a well-designed enterprise data warehouse, master-data management process, or source-system correction. Very large or frequently refreshed datasets may require transformations upstream. Web sources may change without notice, and APIs may impose pagination or rate limits. Desktop development can conceal operational issues that appear only during scheduled refresh.

Best-Practice Checklist

• Understand the source owner, grain, refresh process, and permitted use.
• Profile data before transforming it.
• Apply explicit data types and appropriate locale settings.
• Preserve exceptions rather than silently deleting them.
• Validate keys before merging.
• Use append only when schemas and meanings are compatible.
• Reduce rows and columns early, while checking query folding.
• Parameterize environment-specific values.
• Encapsulate repeated logic in functions.
• Name and document queries according to their role.
• Test refresh with missing, empty, malformed, and changed inputs.
• Reconcile important outputs with an independent control total.
• Protect credentials and sensitive information.

Chapter Summary

Power Query is a practical ETL and data-preparation environment for Power BI. It connects to diverse sources, records transformations as ordered steps, and uses M to express both graphical and custom logic. Professional preparation begins with source understanding and profiling, then applies explicit typing, cleansing, reshaping, merging, appending, and validation.

Data quality should be treated as a controlled process involving rules, detection, disposition, and evidence. Merging enriches rows through key-based joins, whereas appending extends a dataset vertically. Query folding can push eligible operations to a source system and improve performance, but it depends on the connector and transformation sequence. Parameters and functions support reusable, maintainable solutions.

Effective Power Query work also requires governance. Privacy, lineage, access control, exception handling, and reconciliation are as important as technical commands. The strongest solution is not the one with the most transformations; it is the one whose logic is understandable, repeatable, testable, and fit for the intended decision.

Key Terms

Append: Combines rows from two or more tables.

Applied Step: A recorded Power Query transformation step.

Data contract: An agreed specification of required fields, types, rules, and meanings.

Data profiling: Examination of data structure, distributions, types, and quality.

ETL: Extract, transform, and load.

Exception query: A query containing records that fail defined quality rules.

Function: Reusable M logic that accepts inputs and returns an output.

Grain: The level of detail represented by one row.

Join: Combination of tables using matching keys.

Locale: Regional rules governing formats such as dates, decimals, and currency.

M: The functional language used by Power Query.

Merge: A Power Query operation that joins columns from related tables.

Metadata: Information describing data, such as type, owner, and source.

Null: An absent or unknown value.

Parameter: A named value that can be changed independently of query logic.

Pivot: Converts row categories into columns.

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

Unpivot: Converts columns representing categories into attribute-value rows.

Data lineage: Record of where data originated and how it was transformed.

Schema: The structural definition of fields, types, and relationships in a dataset.

Review Questions

Short Answer Questions

1. What is the difference between a merge and an append in Power Query?
2. Why is explicit data typing important in a query?
3. Define query folding.
4. What is the purpose of a parameter?
5. Why should data-quality exceptions be retained rather than silently deleted?

Long Answer Questions

1. Explain the complete Power Query workflow from connection to refresh, including the role of profiling and validation.
2. Discuss the main data-quality dimensions and show how they can be implemented through practical controls.
3. Compare inner, left outer, full outer, and anti joins, giving a suitable business use for each.
4. Explain how M steps, functions, and parameters contribute to maintainability and reuse.
5. Evaluate the performance benefits and limitations of query folding in a Power BI solution.

Analytical / Application Questions

1. A monthly sales file contains duplicate transaction identifiers, blank customer IDs, and amounts stored as text with comma decimal separators. Design a Power Query quality and transformation process, including the treatment of exceptions.
2. A merge between a transaction table and a customer table doubles total sales. Diagnose the likely cause and propose tests and corrective actions.
3. Design a folder-based ingestion solution for regional files that have the same business fields but different column order and occasional extra columns. Explain how you would use a function, schema validation, parameters, and reconciliation controls.

Multiple Choice Questions with Answers

Question 1

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

A. Merge

B. Append

C. Pivot

D. Transpose

Correct Answer: B. Append

Question 2

What does query folding allow Power Query to do?

A. Encrypt every source column

B. Convert all nulls to zero

C. Translate eligible steps for execution by the source system

D. Create a data model relationship automatically

Correct Answer: C. Translate eligible steps for execution by the source system

Question 3

Which value is conceptually different from both null and zero?

A. An error

B. A whole number

C. A date

D. A true value

Correct Answer: A. An error

Question 4

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

A. Inner join

B. Left outer join

C. Full outer join

D. Left anti join

Correct Answer: B. Left outer join

Question 5

What is the main purpose of a Power Query parameter?

A. To store a visual colour

B. To change a named value without rewriting query logic

C. To replace every data type

D. To remove duplicate rows automatically

Correct Answer: B. To change a named value without rewriting query logic

Question 6

Which transformation converts month columns such as January and February into attribute-value rows?

A. Unpivot

B. Merge

C. Group By

D. Append

Correct Answer: A. Unpivot

Question 7

Why should key uniqueness be checked before merging tables?

A. Duplicate keys can multiply rows and inflate totals

B. Unique keys prevent query folding

C. Keys can only contain text

D. Merging removes all unmatched rows

Correct Answer: A. Duplicate keys can multiply rows and inflate totals

Question 8

In an M let expression, what identifies the value returned by the query?

A. The Source step

B. The type declaration

C. The in expression

D. The Navigator window

Correct Answer: C. The in expression

Question 9

Which practice best supports reliable folder-based file ingestion?

A. Assume every file has an identical schema

B. Delete files that fail silently

C. Validate the schema and use a reusable transformation function

D. Load every file without profiling

Correct Answer: C. Validate the schema and use a reusable transformation function

Question 10

What is the most appropriate interpretation of a data-quality profile?

A. Proof that the data is accurate in business terms

B. Evidence about structure and possible quality issues

C. A replacement for data governance

D. A guarantee that refresh will succeed

Correct Answer: B. Evidence about structure and possible quality issues