Data Preparation with Power Query

Introduction

Business intelligence reports are only as reliable as the data on which they are based. In practice, source data is rarely ready for immediate analysis. It may contain inconsistent names, missing values, duplicate records, incorrect data types, unnecessary columns, poorly formatted dates, or information spread across several files and systems. Power Query provides a structured environment for addressing these problems before data is loaded into the Power BI data model.

Power Query is an extract-transform-load (ETL) technology. It extracts data from one or more sources, transforms it through a sequence of documented steps, and loads the result into Power BI or another destination. The transformation process is repeatable: when the source is refreshed, Power Query can execute the same steps again rather than requiring the user to clean the data manually.

This chapter introduces the Power Query Editor and the core concepts needed to profile, clean, filter, reshape, combine, and validate data. It also introduces the M language, which is the formula language behind Power Query. The emphasis is practical and foundational, but the underlying principles are important for professional data preparation.

Learning Objectives

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

• explain the role of Power Query in an ETL process;
• distinguish between queries, steps, columns, rows, and data types;
• profile data using column quality, distribution, and statistics tools;
• clean missing, duplicate, inconsistent, and incorrectly typed data;
• filter rows and select or remove columns appropriately;
• reshape data by splitting, merging, pivoting, unpivoting, and grouping;
• merge related tables and append tables with compatible structures;
• read and write basic M expressions;
• validate transformations and design maintainable queries;
• identify governance, privacy, performance, and reliability issues in data preparation.

1. Power Query as an ETL Framework

ETL consists of three connected activities:

1. Extract data from files, databases, web services, applications, or other sources.
2. Transform the data by correcting errors, changing structure, combining sources, and applying business rules.
3. Load the prepared data into a Power BI model or another analytical destination.

Power Query performs these activities through queries. A query is a set of instructions that describes how to obtain and transform data. A query does not merely store a static copy of the data. Instead, it stores the connection information and transformation logic needed to reproduce the result during refresh.

A query normally contains sequential steps. For example:

• connect to an Excel workbook;
• select a worksheet;
• promote the first row to column headers;
• remove empty rows;
• change data types;
• filter transactions to the current financial year;
• merge with a product table;
• calculate a derived column;
• load the result.

The Applied Steps pane displays these operations. Selecting a step allows the analyst to inspect the intermediate result. This makes the transformation process visible and auditable.

Power Query is distinct from the Power BI data model. Power Query prepares data; the model organizes relationships and supports analysis. DAX is primarily used after loading for measures, calculated columns, and analytical expressions. A useful general principle is:

Perform data-shaping and source-correction tasks in Power Query; perform analytical calculations that depend on filter context in the model using DAX.

This is not an absolute rule, but it supports clarity and efficiency.

2. The Power Query Interface and Data Model of a Query

The Power Query Editor commonly includes the following areas:

• Ribbon: commands for connecting, transforming, combining, and managing queries.
• Queries pane: a list of queries, including staging queries and final output queries.
• Data preview: a sample or preview of the current table.
• Applied Steps: the ordered sequence of transformations.
• Formula bar: the M expression for the selected step, when enabled.
• Status information: column quality, distinct values, and error indicators when profiling is enabled.

A table in Power Query consists of rows and columns, but M treats values more generally. M supports primitive values such as text, number, date, time, duration, logical values, null, and binary data. It also supports structured values:

• a list is an ordered sequence of values;
• a record is a set of named fields;
• a table is a collection of records;
• a function is reusable logic that accepts inputs and returns an output.

The use of structured values explains why Power Query can process nested JSON, XML, folders, and other semi-structured sources.

Data types

Correct data types are essential. Common types include:

A column containing numbers stored as text may appear correct but cannot be reliably aggregated. A date interpreted using the wrong regional convention can silently produce incorrect results. Data types should therefore be checked deliberately rather than accepted automatically.

3. Data Profiling Before Transformation

Data profiling is the systematic examination of data to understand its structure, completeness, validity, and variation. Profiling should occur before extensive transformation because it reveals the nature of the problem and helps prevent inappropriate cleaning decisions.

Power Query provides three useful profiling perspectives:

• Column quality: indicates valid values, errors, and empty values.
• Column distribution: shows the number of distinct and unique values.
• Column profile: provides statistics such as minimum, maximum, average, and error counts where applicable.

The profile may be calculated over the top 1,000 rows or over the entire dataset, depending on the selected setting. This distinction matters. A sample can be useful for rapid exploration but may fail to reveal errors occurring later in a large source.

Questions to ask during profiling

For each important column, consider:

• What does one row represent?
• Is the column a measure, identifier, category, date, or description?
• Are values missing, blank, or null?
• Are there leading or trailing spaces?
• Are categories written consistently?
• Are there impossible or out-of-range values?
• Are supposed identifiers unique?
• Are there duplicate records?
• Does the data type match the intended meaning?

Profiling is not limited to finding technical errors. It also examines semantic quality. For example, UK, U.K., and United Kingdom may all be technically valid text values but represent one business category.

4. Cleaning and Standardizing Data

Removing unnecessary data

Remove columns that are not required for reporting, especially confidential fields, technical fields, and duplicated attributes. Removing unnecessary data improves readability, may reduce model size, and supports privacy by limiting exposure.

Remove rows that have no analytical meaning, such as repeated headers, blank separators, notes beneath a report, or totals already represented by detail rows. Care is required: a row that appears blank may contain meaningful formatting or a partially populated record.

Handling nulls and blanks

A null represents the absence of a value. A blank text value is text of length zero, and a whitespace value may contain spaces. These conditions are not always equivalent. Cleaning procedures should distinguish between them where the business meaning differs.

Possible treatments include:

• leave the value as null when absence is meaningful;
• replace it with a documented category such as Unknown;
• replace it with zero only when zero genuinely means none;
• remove the row if the missing value makes the record unusable;
• derive a value from another trusted column.

Replacing every missing number with zero is dangerous. For example, a missing sales amount is not necessarily zero sales.

Text standardization

Useful transformations include trimming spaces, cleaning non-printing characters, changing case, replacing values, and standardizing punctuation. For example, the following values may require normalization:

East, east, EAST, and East

A typical standardization sequence is:

1. clean non-printing characters;
2. trim leading and trailing spaces;
3. apply a consistent case convention;
4. replace known alternative labels;
5. validate the resulting categories.

Text transformations should be applied carefully to identifiers. Changing case may be harmless for descriptive labels but may alter a case-sensitive code.

Duplicate records

Duplicates can arise from repeated file extracts, accidental re-entry, or joining tables at an inappropriate granularity. Removing duplicates is appropriate only when the selected columns define record identity. If a transaction table contains several legitimate lines for one order, removing duplicate order numbers would destroy valid information.

Before removing duplicates, identify the intended grain of the table. Grain means what one row represents, such as one order line, one customer, or one customer per month.

Errors and error handling

An error may result from converting invalid text to a number, dividing by an invalid value, or applying a function to an incompatible type. Errors should not simply be hidden. Appropriate actions include:

• correcting the source or transformation logic;
• replacing the error with a documented value;
• removing the affected row when justified;
• retaining an error flag for data-quality monitoring.

A robust design often creates a separate validation query or column that records whether a row passed specified checks.

5. Filtering, Selecting, and Reshaping Data

Filtering rows

Filtering limits the rows passed to later steps. Common filters include date ranges, categories, numeric thresholds, and nonblank conditions. Filters should reflect a clear business requirement. A filter such as “current year” may change over time and should be designed dynamically where appropriate rather than hard-coded to one year.

Power Query filters can be applied through the interface or expressed in M. A simple filter might be represented conceptually as:

Table.SelectRows(Source, each [Status] = "Open")

This returns rows for which the Status field equals Open.

Selecting and removing columns

Selecting only required columns is often preferable to removing a long list of unwanted columns, particularly when source structures change. However, selecting columns by name can cause a refresh error if a source column is renamed. The choice depends on whether strict schema control or flexibility is more important.

Splitting and extracting columns

A single column may contain several attributes, such as London - UK or 2025/04/15. Splitting by delimiter, number of characters, or position can separate these components. Extraction can obtain text before or after a delimiter, or a portion of a date or code.

The analyst should check whether the delimiter is consistent. If not, a conditional transformation or a custom function may be needed.

Pivoting and unpivoting

A pivot converts distinct values in a column into separate columns. It is useful when categories should become fields, but it can increase the width of a table and create problems if new categories appear.

An unpivot converts columns into attribute-value rows. It is particularly important for analytical models because it converts cross-tabulated reports into a normalized, flexible structure.

For example, this wide table:

can be unpivoted into Product, Month, and Sales columns. The resulting structure supports filtering and grouping by month without requiring a separate column for every period.

Grouping and aggregation

Grouping combines rows according to one or more keys and calculates summaries such as sum, count, minimum, maximum, or average. Grouping is useful for summary tables, validation, and reducing data volume. It should not replace detailed transaction data when the report requires drill-through or transaction-level analysis.

6. Combining Queries: Merge and Append

Merge

A merge joins two tables using one or more matching columns. It is conceptually similar to a relational database join. Typical uses include adding product attributes to sales transactions or matching employee records to departments.

Common join types include:

After a merge, the related table commonly appears as a nested table column. Expanding that column selects the fields to add.

A major risk is row multiplication. If the lookup table contains more than one row for a matching key, one transaction may become several rows after expansion. Therefore, the relationship key should be checked for uniqueness when a one-to-one or many-to-one result is expected.

Append

An append stacks tables vertically. It is appropriate when tables have the same business meaning but contain different rows, such as monthly sales files or regional extracts. Columns are matched by name rather than simply by position; unmatched columns produce nulls.

Append is not a substitute for merge. Merge adds attributes horizontally through a key, whereas append combines records vertically.

7. Core M Concepts

Power Query transformations are written in M. The interface generates M code, so beginners can work productively without writing every expression manually. Understanding basic M improves debugging and supports transformations that are difficult to create through menus.

M is case-sensitive in many contexts and uses a let ... in structure. A simple query has the form:

let
    Source = Excel.Workbook(File.Contents("C:\Data\Sales.xlsx"), null, true),
    Sheet = Source{[Item="Sales", Kind="Sheet"]}[Data],
    Headers = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true])
in
    Headers

The names before the equals sign are step names. The expression after in identifies the final result returned by the query.

A transformation can refer to an earlier step:

let
    Source = ...,
    Filtered = Table.SelectRows(Source, each [Amount] > 0),
    Typed = Table.TransformColumnTypes(Filtered, {{"Amount", type number}})
in
    Typed

The each keyword represents a function applied to each row. [Amount] refers to the value in the Amount column for the current row.

Useful M functions include:

• Table.SelectRows for filtering;
• Table.SelectColumns and Table.RemoveColumns for column management;
• Table.TransformColumnTypes for assigning types;
• Table.AddColumn for derived columns;
• Table.ReplaceValue for replacements;
• Table.Distinct for duplicate removal;
• Table.Combine for appending tables;
• Table.NestedJoin for merging tables;
• Text.Trim, Text.Upper, and Text.Lower for text treatment;
• Date.Year, Date.Month, and related functions for date attributes.

M also supports comments. A single-line comment begins with //; a block comment is enclosed by / and /. Comments should explain business logic or unusual assumptions rather than restate obvious commands.

8. Validation and Repeatable ETL Design

A transformation is not complete when it runs without an error. It is complete when the result is fit for its intended analytical purpose. Validation should occur at several levels.

Structural validation

Check that:

• required columns exist;
• data types are appropriate;
• expected tables are present;
• key columns are not unexpectedly null;
• row counts are plausible.

Content validation

Check that:

• dates fall within an expected range;
• amounts are within reasonable limits;
• categories belong to an approved list;
• identifiers have the expected format;
• totals reconcile to a trusted source where possible.

Relationship validation

After merging, examine unmatched keys using an anti-join. If 4% of sales products do not match the product table, the issue should be investigated rather than ignored.

Reconciliation

A reconciliation compares a transformed result with a control total. For example, if a source reports total invoice value of 250,000 and the prepared table reports 249,200, the difference must be explained. Differences may result from excluded cancellations, duplicate removal, currency conversion, or an error.

Query organization

A maintainable solution often separates:

• staging queries, which connect to and lightly standardize sources;
• reference queries, which reuse a staging result without duplicating extraction logic;
• transformation queries, which apply business rules;
• output queries, which load only the required tables.

Disable load for intermediate queries when they are not needed in the model. This reduces clutter and can prevent unnecessary model size.

Best practices include:

1. Give queries and steps meaningful names.
2. Keep transformations in a logical order.
3. Set data types deliberately, preferably near the point where a column becomes stable.
4. Filter early when it is safe and beneficial.
5. Remove unnecessary columns early.
6. Avoid repeated manual steps that can be parameterized.
7. Document assumptions and source limitations.
8. Test refresh using new source data, not only the original sample.
9. Preserve raw sources where governance requires reproducibility.
10. Use query folding when available.

Query folding occurs when Power Query translates compatible transformations into the source system’s native query, such as SQL. Folding can improve performance because filtering and aggregation may occur in the database rather than after all rows are transferred. Not every transformation folds, and folding behavior depends on the connector and sequence of steps. Avoid assuming that a transformation folds merely because it appears simple.

9. Applied Case: Regional Retail Sales

The following is an illustrative case.

Background

A retailer receives monthly sales workbooks from three regions. Each workbook contains order lines, but the files differ slightly. One region labels the customer field Customer Name, another uses Customer, and the third includes a title row above the headers. Product codes are sometimes stored as numbers, and regional names contain inconsistent spacing.

Problem

The management team needs a reliable Power BI report showing sales by month, region, product category, and customer. Manual consolidation has produced duplicated orders and unexplained differences from the finance control total.

Analysis

Initial profiling identifies the following issues:

• one workbook contains a repeated header inside the data;
• 2.1% of product codes do not match the product reference table;
• several region labels contain trailing spaces;
• sales dates are interpreted as text in one file;
• one file includes a subtotal row for each region;
• the same order number appears on multiple lines because orders contain multiple products.

The last observation is not an error. The grain is one order line, so order numbers are not expected to be unique.

Approach and solution

First, each workbook is imported through a folder-based process. A reusable transformation function standardizes column names, removes report titles and subtotal rows, promotes headers, trims text, and assigns types. The three regional results are appended because they have the same business meaning.

The product reference table is cleaned separately. Product codes are converted to text in both tables, because a code such as 00125 must not become 125. A left outer merge adds product category and description. An anti-join creates a list of unmatched product codes for investigation.

The final sales query calculates line value from quantity and unit price, while a validation query compares the sum of line values with the finance control total. The model retains the detailed order-line table, allowing managers to drill into individual transactions.

Outcome

The process becomes refreshable: adding a new monthly workbook to the folder triggers the same transformation logic. Regional labels are consistent, dates are usable in the model, and unmatched product codes are visible rather than silently omitted. The finance difference is traced to subtotal rows that had previously been included in the manual consolidation.

Learning points

• profiling should precede cleaning;
• table grain determines whether duplicates are legitimate;
• append is appropriate for structurally similar regional files;
• merge adds descriptive attributes through a key;
• identifiers should be typed according to their meaning, not their appearance;
• validation queries turn data quality into a visible management issue.

10. Limitations, Governance, and Responsible Practice

Power Query improves repeatability but does not guarantee data accuracy. If the source is incomplete, biased, or incorrectly defined, a technically successful refresh can still produce misleading results.

Important limitations include:

• source schemas may change unexpectedly;
• credentials and permissions may prevent refresh;
• transformations may behave differently across connectors;
• large datasets may create memory or refresh-time constraints;
• automatic type detection may be wrong;
• regional settings can affect dates and decimal separators;
• a query may conceal business rules that have not been documented.

Privacy and governance also matter. Analysts should avoid importing personal or sensitive fields unless they are required. Access permissions, privacy levels, retention rules, and organizational policies should be followed. Combining sources can create new privacy risks even when each source was separately approved.

Artificial intelligence features can assist with pattern detection or transformation suggestions, but they do not replace profiling, validation, or human accountability. An AI-generated transformation may misunderstand the grain of a table, remove legitimate exceptions, or expose confidential data through an inappropriate workflow. Any automated suggestion should be tested against known cases and documented.

Chapter Summary

Power Query is the data-preparation layer of Power BI. It supports repeatable extraction, transformation, and loading from diverse sources. Effective preparation begins with profiling: understanding table grain, data types, missing values, distributions, duplicates, and errors. Cleaning may involve trimming text, standardizing categories, handling nulls, correcting types, removing irrelevant records, and investigating errors.

Filtering and column selection control the scope of data. Reshaping operations such as splitting, pivoting, unpivoting, and grouping adapt data to analytical requirements. Merge joins tables horizontally through keys, while append stacks compatible tables vertically. The M language records these operations as sequential expressions, usually within a let ... in structure.

Professional solutions validate structure, content, relationships, and control totals. They use meaningful names, staging layers, documentation, appropriate data types, query folding where available, and controlled loading. Responsible Power Query practice also considers privacy, governance, schema changes, performance, and the limitations of automated assistance.

Key Terms

Append: Combining tables vertically by adding rows from one table to another.

Applied Steps: The ordered list of transformations in a Power Query query.

Data profiling: Examining data quality, completeness, distribution, and structure before or during preparation.

Data type: The category defining how a value is interpreted, such as text, number, date, or logical.

ETL: Extract, transform, and load; the process of obtaining, preparing, and delivering data.

Grain: The specific meaning represented by one row in a table.

Inner join: A merge that retains only records with matching keys in both tables.

M: The formula language used by Power Query.

Merge: Combining tables horizontally using one or more matching columns.

Null: A value representing the absence of a value.

Query: A stored set of instructions for obtaining and transforming data.

Query folding: Translating compatible Power Query operations into the source system’s native operations.

Reference query: A query that reuses the result or logic of another query.

Staging query: An intermediate query used to connect to and standardize source data.

Unpivot: Converting columns into attribute-value rows.

Pivot: Converting distinct values into separate columns.

Validation: Checking whether transformed data satisfies structural, content, relational, or reconciliation requirements.

Left outer join: A merge that keeps every row from the first table and matching rows from the second.

Review Questions

Short Answer Questions

1. What are the three stages of an ETL process?
2. Why is identifying the grain of a table important before removing duplicates?
3. What is the difference between a merge and an append?
4. What does a let ... in expression do in M?
5. Why should missing values not automatically be replaced with zero?

Long Answer Questions

1. Explain how data profiling supports reliable Power Query development. Refer to column quality, distribution, statistics, and data types.
2. Describe a professional process for cleaning inconsistent text, nulls, errors, duplicates, and incorrectly typed columns.
3. Explain the main join types available when merging queries and give a suitable business use for each.
4. Discuss query folding, query organization, and naming practices as factors in maintainable and efficient ETL design.
5. Explain how validation queries and reconciliation checks can improve trust in a Power BI report.

Analytical / Application Questions

1. A monthly sales table contains repeated order numbers, subtotal rows, and several product codes with leading zeros. Explain how you would determine which repetitions are legitimate and design a Power Query process to prepare the data.
2. After merging a transaction table with a product table, the row count increases by 8%. Analyze possible causes and describe the checks you would perform before accepting the result.
3. Three regional files contain the same business information but use different column names, date formats, and category labels. Design a refreshable Power Query architecture that standardizes and appends the files while making data-quality exceptions visible.

Multiple Choice Questions with Answers

Question 1

Which activity is the primary purpose of Power Query in Power BI?

A. Creating report page visual formatting

B. Preparing and transforming data before it is loaded into the model

C. Writing all analytical measures in DAX

D. Managing user passwords

Correct Answer: B. Preparing and transforming data before it is loaded into the model

Question 2

What does an append operation do?

A. Adds columns by matching keys

B. Removes duplicate rows

C. Stacks compatible tables vertically

D. Converts rows into columns

Correct Answer: C. Stacks compatible tables vertically

Question 3

Which operation is most appropriate for adding product category to sales transactions using ProductCode?

A. Merge

B. Append

C. Unpivot

D. Transpose

Correct Answer: A. Merge

Question 4

Why can replacing every missing numeric value with zero be misleading?

A. Zero is always converted to text

B. A missing value may not mean that the quantity or amount was zero

C. Power Query cannot store zero

D. Zeros cannot be aggregated

Correct Answer: B. A missing value may not mean that the quantity or amount was zero

Question 5

In a Power Query table, what does the grain describe?

A. The color of a column

B. The number of transformations in a query

C. What one row represents

D. The physical size of a file

Correct Answer: C. What one row represents

Question 6

Which join keeps all rows from the first table and matching rows from the second table?

A. Inner join

B. Left outer join

C. Left anti join

D. Full anti join

Correct Answer: B. Left outer join

Question 7

What is the purpose of the in part of a typical M let ... in expression?

A. It identifies the final result returned by the query

B. It imports a database driver

C. It changes all values to integers

D. It starts a loop over every column

Correct Answer: A. It identifies the final result returned by the query

Question 8

A product code such as 00125 must retain its leading zeros. Which data type is generally most appropriate?

A. Whole number

B. Decimal number

C. Text

D. Duration

Correct Answer: C. Text

Question 9

Which technique is especially useful for identifying transaction keys that do not exist in a reference table?

A. Anti-join

B. Pivot

C. Group by average

D. Transpose

Correct Answer: A. Anti-join

Question 10

What is query folding?

A. Compressing a report into a PDF

B. Translating compatible Power Query steps into operations performed by the source system

C. Converting every table into a record

D. Combining all queries into one worksheet

Correct Answer: B. Translating compatible Power Query steps into operations performed by the source system