Data Acquisition and Transformation with Power Query

Introduction

Business analytics depends less on sophisticated models than on the quality, structure, and currency of the data supplied to them. In practice, data is rarely available in a single clean workbook. It may be distributed across CSV files, Excel workbooks, databases, web services, enterprise applications, and departmental folders. It may also contain inconsistent labels, missing values, duplicate records, incorrect data types, and changing file layouts.

Power Query is Excel’s environment for extracting, transforming, and loading data. It enables analysts to create repeatable processes that connect to sources, apply documented transformations, and load results into worksheets or the Data Model. Instead of manually copying and editing data each reporting period, an analyst can refresh a query and reproduce the same transformation sequence.

Power Query is therefore both a practical Excel feature and an example of an extract–transform–load (ETL) workflow. Its value lies not only in saving time, but also in improving consistency, traceability, auditability, and readiness for digital analytics.

Learning Objectives

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

• explain the purpose and stages of ETL;
• connect Power Query to files, folders, databases, web sources, and organizational systems;
• inspect and profile data before analysis;
• clean, filter, split, combine, and reshape data;
• distinguish between merging and appending queries;
• understand query dependencies and refresh behavior;
• design repeatable and governed data-preparation workflows;
• evaluate limitations, risks, and ethical issues in data acquisition.

1. Power Query and the ETL Model

ETL describes a sequence of operations:

1. Extract data from one or more sources.
2. Transform the data into a consistent analytical structure.
3. Load the result into a destination such as an Excel table, PivotTable source, or Data Model.

The process is often represented as:

Source systems → Extract → Transform → Load → Analysis and reporting

Extraction does not necessarily mean copying all source data permanently. Power Query commonly stores connection information and transformation instructions. During refresh, it reconnects to the source and re-executes the steps.

A query is a named sequence of transformations. Each step produces a new table or value from the previous step. Power Query records these steps using the M language, even when the user works through the graphical interface. The graphical interface is accessible to beginners; M provides additional control for advanced users.

Queries, connections, and outputs

A connection describes how Power Query reaches a source. A query describes what is done with the retrieved data. The load destination specifies where the result is placed. These are related but distinct concepts.

For example, a query may connect to a folder, select CSV files, promote the first row to headers, change data types, remove errors, and load the result to the Data Model. The original files remain unchanged.

Power Query is not a replacement for a transactional database. It is primarily a data-acquisition and preparation environment. Large-scale storage, access control, transaction processing, and enterprise master-data management may require specialized systems.

2. Connecting to Data Sources

Power Query supports many source types. The appropriate connector depends on the structure, scale, security requirements, and refresh needs of the data.

File sources

CSV files are common because they are portable and easy to generate. However, a CSV file does not inherently preserve data types, formulas, relationships, or formatting. Analysts must check delimiters, text encoding, decimal symbols, date conventions, and quotation marks.

Excel workbooks can contain tables, named ranges, worksheets, and hidden objects. Connecting to a defined Excel Table is generally more reliable than connecting to an arbitrary worksheet range because the table expands as records are added.

Folder sources

The Folder connector is useful when data arrives as repeated files, such as daily transactions or monthly regional reports. A typical folder query:

1. connects to the folder;
2. filters out temporary or irrelevant files;
3. selects files with the required extension;
4. combines their contents;
5. applies a consistent transformation to each file;
6. retains useful metadata such as file name and date modified.

The files should have a stable structure. If one file contains different column names or an extra title row, the combine process may fail or produce unreliable results. A robust workflow filters files using explicit rules rather than assuming that every file in a directory is valid.

Database sources

Database connectors can retrieve tables, views, or the results of SQL statements. Analysts should use the least privilege necessary: read access is usually sufficient for reporting. They should also understand whether transformations are executed by the source database or locally in Excel.

Query folding occurs when Power Query translates transformations into operations performed by the source system. Filtering rows early, selecting only required columns, and avoiding unnecessary local operations can improve performance when folding is available. Not every transformation folds, and folding behavior depends on the connector and source.

Web and organizational sources

Web pages are often designed for human viewing rather than stable data exchange. A structured API or OData feed is generally preferable to scraping an unstable page. Web connections may require authentication tokens, privacy settings, rate limits, or pagination.

Organizational connectors such as SharePoint and OneDrive can support collaborative reporting, but ownership and permissions must be managed carefully. A query should not depend on an employee’s personal desktop path when the workbook is intended for organizational use.

3. Profiling Data Before Transformation

Data profiling is the systematic inspection of a dataset before decisions are made about cleaning or analysis. It asks questions such as:

• What are the columns and their data types?
• How many rows are present?
• Which values are missing?
• Are identifiers unique?
• Are there duplicate records?
• What are the minimum and maximum dates?
• Do categories contain spelling or capitalization variants?
• Are numeric values plausible?

Power Query provides column quality, column distribution, and column profile views. These tools help identify errors, empty values, and unexpected patterns. Profiling should be performed on a sufficiently representative dataset. A preview may not reveal problems occurring only in later records.

Data types

Correct data types are essential. Common types include text, whole number, decimal number, date, date/time, logical, duration, and binary. A date stored as text cannot reliably support time-based grouping. A product code containing leading zeros should usually remain text; converting it to a number would change values such as 00125 to 125.

Automatic type detection is convenient but should be reviewed. Locale matters: 03/04/2025 may mean 3 April or March 4 depending on the convention. Explicitly specifying the locale and intended type reduces ambiguity.

Data quality dimensions

Important quality dimensions include:

• Completeness: whether required values are present;
• Validity: whether values conform to rules and permitted formats;
• Accuracy: whether values represent the real-world facts;
• Consistency: whether the same concept is represented uniformly;
• Uniqueness: whether records are duplicated;
• Timeliness: whether data is current enough for the decision.

Power Query can improve structure and consistency, but it cannot automatically establish whether a value is factually accurate. A correctly formatted but incorrect sales amount remains incorrect.

4. Core Transformation Operations

Selecting, removing, and renaming columns

Remove irrelevant columns early to reduce complexity and processing volume. Rename columns using clear, stable business names. Avoid names that depend on temporary source layouts, such as Column1, unless they are subsequently corrected.

Filtering rows

Filters can exclude blank records, cancelled transactions, test data, or dates outside the reporting period. A filter should reflect an explicit business rule. For example, excluding returns is not merely a technical operation; it changes the meaning of a sales measure and must be documented.

Replacing values

Replacement is useful for correcting known variants such as UK, U.K., and United Kingdom. It should not be used to conceal uncertain data. Maintain a mapping table when categories are numerous or likely to change.

Splitting and combining columns

A full name may be split into first and last names; a product code may be divided into category and sequence components. Conversely, separate fields may be combined into a display label. Analysts should preserve original columns when the transformation is reversible or when auditability matters.

Handling missing values and errors

Missing values may mean unknown, not applicable, not collected, or zero. These meanings are not interchangeable. Replacing every null with zero can create serious analytical errors. Error values can be removed, replaced, or retained for investigation. A professional workflow records the reason for the chosen treatment.

Duplicates

Removing duplicates requires a definition of duplicate. Entire-row duplicates differ from duplicate customer IDs or repeated transactions. Before removing records, determine whether repetition is an error or a legitimate business event.

Grouping and aggregation

Grouping can calculate totals, averages, counts, minimums, and maximums by categories such as region or product. Aggregation changes the grain of the data. If transaction-level data is grouped by month, individual transactions are no longer available in the resulting table unless retained separately.

Pivoting and unpivoting

A wide table stores categories as columns, while a long table stores categories as rows. Analytical systems often prefer a long structure because it supports flexible filtering and aggregation.

For example, this wide structure:

can be unpivoted into:

Unpivoting is especially useful when monthly, departmental, or product measures have been placed into separate columns. Pivoting reverses the operation when a report requires categories as columns.

5. Merging and Appending Queries

These operations are frequently confused.

Merge

A merge combines columns from two tables by matching key values. It is conceptually similar to a database join. For example, transaction data can be merged with a product table using ProductID to add product category and standard cost.

Common join types include:

• Left outer: keeps every row from the first table and matching rows from the second;
• Inner: keeps only rows with matches in both tables;
• Right outer: keeps every row from the second table and matching rows from the first;
• Full outer: keeps all rows from both tables;
• Anti join: identifies rows without a match.

The choice depends on the business question. A left merge is often appropriate when the transaction table is the primary table and descriptive attributes are being added.

A key should be stable and appropriately unique. If the lookup table contains multiple rows for one key, a merge may multiply transaction rows and inflate totals. Analysts should test key uniqueness before merging.

Append

An append stacks rows from tables with similar columns. It is appropriate for combining January, February, and March transaction tables or regional files. Columns are matched by name, not necessarily by position. Missing columns produce null values.

Appending is valid only when the tables represent the same type of entity and have compatible meanings. Appending customers to suppliers merely because both tables have a Name column would be conceptually invalid.

6. Query Dependencies and Refreshability

A query dependency is a relationship in which one query uses the output of another. For example:

Raw files → Staging query → Clean transactions → Sales summary

This architecture separates source-specific work from business calculations. A staging query imports and standardizes data. A downstream query performs analysis-ready transformations. Queries that support other queries may be configured not to load directly to a worksheet, reducing clutter and duplication.

Refresh executes the dependency chain in the required order. A refresh can fail because a file was moved, credentials expired, a column was renamed, a database is unavailable, or an expected data type changed.

A repeatable query should:

• use stable source locations;
• filter irrelevant files explicitly;
• specify data types deliberately;
• avoid unnecessary manual steps;
• document assumptions;
• preserve meaningful source metadata;
• include validation checks;
• be tested with changed and incomplete inputs.

Refreshability is not the same as correctness. A query can refresh successfully while producing wrong results if a source definition or business rule has changed.

7. Worked Application: Monthly Retail Sales

A retailer receives one CSV file per month. Each file contains OrderID, OrderDate, ProductCode, Region, Quantity, and UnitPrice. Product information is stored in an Excel table containing ProductCode, ProductName, and Category.

Problem

The reporting team manually copies files into a workbook. Product codes are sometimes interpreted as numbers, cancelled orders are included in sales, and regional labels differ between files.

Approach

1. Connect to the folder containing monthly CSV files.
2. Filter for .csv files and exclude files beginning with Test_.
3. Combine files using a sample-file transformation.
4. Promote headers and assign explicit data types.
5. Standardize region labels using a mapping table.
6. Filter out rows whose status is Cancelled.
7. Trim text fields and preserve leading zeros in ProductCode.
8. Merge the transaction query with the product table using ProductCode.
9. Use an anti-join check to identify transactions with no product match.
10. Load the clean transaction table to the Data Model.

If sales value is required, the row-level calculation is:

where SalesValue_i is the value for transaction i, Quantity_i is the quantity sold, and UnitPrice_i is the price per unit. The formula assumes that quantity and price use compatible units and that discounts, taxes, returns, and currency conversion have either been incorporated or deliberately excluded.

Suppose one transaction has 8 units at £12.50 per unit. Then:

The query can then group the clean data by month, region, and category. If 2,000 transactions are loaded and 15 have no matching product code, the unmatched records should be investigated rather than silently discarded. They may indicate new products, typing errors, or incomplete master data.

Outcome

The retailer replaces repetitive manual copying with a refreshable process. New monthly files are added to the folder, the query is refreshed, and the report updates. The workflow also exposes data-quality exceptions and creates a clearer audit trail.

Learning points

The technical steps are valuable because they encode business rules. Filtering cancelled orders defines the population of sales; preserving product-code text protects identifiers; and validating unmatched keys protects the reliability of category analysis.

8. Governance, Ethics, and Security

Data acquisition involves more than technical convenience. Analysts should consider:

• whether the organization has a lawful and legitimate basis for using personal data;
• whether sensitive fields are necessary for the analytical purpose;
• who can access source data and query outputs;
• whether credentials are stored securely;
• whether data is transferred across jurisdictions;
• how long extracted data should be retained;
• whether transformations could unfairly exclude or misclassify groups.

Power Query should not be used to bypass access controls. Credentials must not be embedded in shared files or exposed in query expressions. Personal data should be minimized, protected, and de-identified where possible. Data lineage should identify the source, transformation logic, responsible owner, and refresh time.

AI-assisted features may help generate formulas, M expressions, or transformation suggestions. However, generated code must be reviewed for privacy, correctness, hidden assumptions, and inappropriate data transmission. An AI tool should not receive confidential source data unless approved by organizational policy.

9. Limitations and Best Practices

Power Query is powerful, but it has limitations. Very large datasets may exceed workbook capacity or become slow to refresh. Complex transformations may be difficult to maintain if they are implemented as opaque custom code. Source systems may change without notice. Some connectors support query folding while others do not. Refresh in a desktop workbook may also behave differently from refresh in a cloud service.

Recommended practices include:

1. Define the analytical purpose and grain of the output before transforming.
2. Keep raw source data separate from transformed outputs.
3. Select only required columns and filter early where appropriate.
4. Use descriptive query and step names.
5. Treat identifiers as text when leading zeros or nonnumeric characters matter.
6. Validate keys before merging.
7. Use mapping tables for controlled category standardization.
8. Record assumptions about dates, currencies, status codes, and exclusions.
9. Create exception queries for errors, unmatched records, and missing fields.
10. Test refresh with new files and altered source conditions.
11. Control credentials and document data ownership.
12. Prefer a stable API, database view, or organizational connector over fragile web scraping.

Chapter Summary

Power Query provides a repeatable environment for acquiring and preparing data in Excel. Its ETL model separates extraction from transformation and loading. Analysts can connect to files, folders, databases, web sources, and organizational systems; profile data; assign types; correct inconsistencies; handle missing values; reshape tables; and combine datasets through merges and appends.

A reliable workflow is designed around business meaning as well as technical operations. Merges require valid keys, appends require compatible entities, and transformations can alter the grain and interpretation of data. Query dependencies support modular design, while refreshability supports recurring reporting. Nevertheless, a successful refresh does not guarantee accurate results. Governance, security, documentation, validation, and ethical handling of personal data remain essential.

Key Terms

ETL: Extract–transform–load process for moving data from sources into an analytical destination.

Power Query: Excel environment for connecting to, transforming, and loading data.

Query: A named sequence of source connections and transformation steps.

Connector: A mechanism for accessing a particular type of data source.

Data profiling: Examination of structure, values, missingness, errors, and distributions.

Data type: The interpretation assigned to a field, such as text, date, or decimal number.

Query folding: Translation of Power Query operations into source-system operations.

Staging query: Intermediate query used to import and standardize source data.

Merge: Combining columns from tables by matching key values.

Append: Combining rows from tables with compatible structures.

Join key: Field or fields used to match records between tables.

Left outer join: Join retaining all rows from the first table.

Anti-join: Operation identifying records without a match.

Pivot: Reshaping row categories into columns.

Unpivot: Reshaping category columns into rows.

Refresh: Re-execution of source connections and transformation steps.

Data lineage: Record of a dataset’s origin, transformations, and destination.

Query dependency: Relationship in which one query uses another query’s output.

Query grain: The level represented by each row in a dataset.

Data quality: Fitness of data in terms of completeness, validity, accuracy, consistency, uniqueness, and timeliness.

Review Questions

Short Answer Questions

1. What are the three stages of ETL?
2. Why should product codes with leading zeros usually be stored as text?
3. What is the difference between merging and appending queries?
4. What is query folding?
5. Why is profiling data important before transformation?

Long Answer Questions

1. Explain how a folder-based Power Query solution can support monthly reporting. Include potential failure points.
2. Describe the role of data types, missing values, duplicates, and errors in data preparation.
3. Compare left, inner, full, and anti joins and give an appropriate business use for each.
4. Explain how query dependencies improve maintainability and auditability.
5. Discuss governance, privacy, and security issues when Power Query connects to organizational systems.

Analytical / Application Questions

1. A merge increases a transaction table from 10,000 to 12,400 rows. What might have caused this, and how would you investigate it?
2. Design a Power Query workflow for combining weekly inventory files while preserving file-level audit information.
3. A report refreshes successfully but regional sales have increased unexpectedly. Identify at least five validation checks that could determine whether the change is genuine or caused by a transformation error.

Multiple Choice Questions with Answers

Question 1

Which sequence correctly represents ETL?

A. Evaluate, test, load

B. Extract, transform, load

C. Enter, total, link

D. Export, type, list

Correct Answer: B. Extract, transform, load

Question 2

Which operation combines columns from two tables using matching keys?

A. Append

B. Unpivot

C. Merge

D. Group

Correct Answer: C. Merge

Question 3

What is the main purpose of an anti-join?

A. To identify records without a match

B. To sort records alphabetically

C. To convert columns into rows

D. To remove all duplicate rows

Correct Answer: A. To identify records without a match

Question 4

Which data type is generally most appropriate for a product code such as 00125?

A. Whole number

B. Decimal number

C. Date

D. Text

Correct Answer: D. Text

Question 5

A table contains 6 units sold at £15 per unit. What is the sales value under the stated row-level formula?

A. £21

B. £90

C. £105

D. £150

Correct Answer: B. £90

Question 6

Which operation is most appropriate for stacking January, February, and March tables with the same structure?

A. Append

B. Merge

C. Pivot

D. Transpose

Correct Answer: A. Append

Question 7

What does query folding mean?

A. Encrypting a query before storage

B. Converting rows into columns

C. Pushing compatible transformations back to the source system

D. Combining multiple worksheets into one

Correct Answer: C. Pushing compatible transformations back to the source system

Question 8

Why can an automatically detected date type be risky?

A. Dates cannot be loaded into the Data Model

B. Locale conventions may interpret the same text differently

C. Dates always remove duplicate records

D. Date columns cannot be filtered

Correct Answer: B. Locale conventions may interpret the same text differently

Question 9

What is a likely consequence of duplicate keys in a lookup table during a merge?

A. Some transaction rows may be multiplied

B. All source files are deleted

C. The query becomes an append operation

D. The data types are automatically encrypted

Correct Answer: A. Some transaction rows may be multiplied

Question 10

Which practice best supports a repeatable Power Query workflow?

A. Relying on personal desktop paths

B. Manually copying new records into the output

C. Filtering files with explicit rules and documenting assumptions

D. Replacing every missing value with zero

Correct Answer: C. Filtering files with explicit rules and documenting assumptions