Essential Data Transformations

Introduction

Power BI reports are only as reliable as the data model behind them. Data commonly arrives from operational systems, spreadsheets, application programming interfaces, text files, and databases in forms designed for recording transactions rather than analysis. Column names may be unclear, dates may be stored as text, categories may be inconsistent, and information may be arranged in a layout intended for human reading. Power Query provides a repeatable environment for preparing such data before it enters the Power BI data model.

Data transformation is the process of changing the structure, content, and representation of data while preserving its intended meaning. In Power Query, transformations are recorded as sequential applied steps. This creates an auditable and refreshable data-preparation process rather than a one-time manual edit.

This chapter examines the essential transformations used in Power Query: selecting, renaming, reordering, splitting, merging, replacing, filling, pivoting, unpivoting, grouping, sorting, and filtering. It also explains data types, locale settings, quality controls, and the professional consequences of poorly designed transformations.

Learning Objectives

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

• select, remove, rename, and reorder columns and rows;
• split and merge columns appropriately;
• replace inconsistent values and fill missing values cautiously;
• apply suitable data types and locale settings;
• distinguish pivoting from unpivoting;
• group, sort, and filter data for analysis;
• design transformation steps that are repeatable, transparent, and robust;
• identify common risks involving missing, duplicated, ambiguous, or culturally formatted data.

1. The Transformation Pipeline

A typical Power Query workflow follows a logical sequence:

1. Connect to a source.
2. Inspect the structure and quality of the data.
3. Select the required tables, columns, and rows.
4. Correct names, types, formats, and values.
5. Reshape the data where necessary.
6. Validate the results.
7. Load the query or create a reusable function.

Power Query records each operation in the Applied Steps pane. A step is generally expressed in the M language. For example:

= Table.SelectColumns(Source, {"OrderID", "OrderDate", "SalesAmount"})

This step keeps only the specified columns. Although users can work primarily through the graphical interface, understanding that transformations are executable instructions helps explain why step order matters.

Query folding

When the source is a relational database, Power Query may translate some transformations into the source system's query language. This process is called query folding. Filtering rows and selecting columns early can reduce the amount of data transferred and improve performance. However, folding is source- and transformation-dependent; it should be checked rather than assumed.

Step order

Transformations are not always commutative. Filtering before grouping can produce a different result from grouping before filtering. Similarly, changing a column to numeric type before calculating is appropriate, while changing it after a calculation may be too late. A good general principle is to perform structural simplification early, type assignment before calculations, and business-specific transformations only after the data has been understood.

2. Selecting, Removing, Renaming, and Reordering

Selecting and removing columns

Select columns that are required for the analytical purpose of the query. Removing irrelevant columns reduces model size, improves readability, and reduces the possibility that users will interpret unused fields incorrectly. Common operations include Choose Columns, Remove Columns, and Remove Other Columns.

A caution is necessary: removing a column can break later steps that refer to it. It can also remove a field needed for auditing or future analysis. Before deletion, determine whether the field is needed for a key, relationship, validation check, or traceability.

Selecting and filtering rows

Rows may be filtered by value, condition, date range, or error status. For example, a transaction query might retain only records where Status equals “Completed.” Filters should reflect an explicit business rule. A filter that silently excludes nulls, returns, or cancelled transactions can distort measures.

Renaming columns

Column names should be meaningful, consistent, and suitable for report users. “Amt” might be renamed “Sales Amount,” and “Cust_ID” might become “Customer ID.” Avoid names that are ambiguous, excessively abbreviated, or dependent on a source system's internal terminology.

Names should also be stable. If a source file changes “Sales Amount” to “Revenue,” a step that expects the old name may fail. Where source variation is expected, consider documenting the dependency or designing a controlled ingestion process.

Reordering columns

Column order does not normally change analytical results, but it affects usability. A practical order places identifiers first, descriptive attributes next, dates after them, and measures toward the end. Reordering is especially useful in staging queries and exported tables, but it should not be confused with changing the semantic role of a column.

3. Splitting and Merging Columns

Splitting columns

Split Column separates one field into multiple fields. Typical delimiters include commas, spaces, hyphens, and slashes. A full name may be split into given name and surname; a product code may be separated into department and item number; and a date-time text value may be separated into date and time.

Splitting by delimiter is safe only when the delimiter has a consistent meaning. A comma in an address may not separate the same conceptual components in every row. Splitting by a fixed number of characters is appropriate for fixed-width codes but risky for variable-length identifiers.

Example: the value UK-1045-London can be split at hyphens into Country, Store Code, and City. The transformation should specify whether extra delimiters are handled by creating additional columns, merging excess text, or producing errors.

Merging columns

Merge Columns combines fields, often to create a display label or a composite key. For example, City and Region might be combined as Leeds, West Yorkshire. A separator should be chosen deliberately so that the result is readable and unambiguous.

A merged display column should not automatically replace the original columns. The separate fields may be needed for filtering, geographic analysis, or relationships. Similarly, a concatenated key is not necessarily a sound substitute for a properly defined key. If two fields can contain the separator, collisions may occur. For example, concatenating values without a delimiter can make AB + C indistinguishable from A + BC.

4. Replacing Values and Managing Missing Data

Replacing values

Replace Values is useful for correcting known inconsistencies, such as changing UK, U.K., and United Kingdom to one standard representation. It can also replace error markers such as N/A with null, provided that the distinction between unknown, not applicable, and zero is understood.

Replacement may be exact or partial. Partial replacement requires care: replacing “Ltd” might unintentionally alter a longer word or an unrelated code. For controlled transformations, use clearly defined matching rules and validate the resulting frequency distribution.

Null, blank, zero, and error

These values have different meanings:

Replacing null with zero is often incorrect. A missing sales amount does not necessarily mean that no sale occurred. Similarly, replacing missing customer type with “Standard” introduces an unsupported assumption.

Fill Down and Fill Up

Fill Down copies the last non-null value into subsequent null rows; Fill Up copies the next non-null value upward. These operations are useful when a source uses grouped labels, such as a department name shown only on the first row of each section.

Filling is valid only when the source layout supports the assumption that the value applies to the relevant rows. It should not be used indiscriminately in transaction data, where a null may represent a genuine missing attribute.

5. Data Types and Locale Settings

Data types describe how values should be interpreted and calculated. Common Power Query types include Text, Whole Number, Decimal Number, Fixed Decimal Number, Date, Time, Date/Time, Date/Time/Timezone, Logical, and Duration.

Assign types deliberately rather than relying solely on automatic detection. A date stored as text cannot reliably support date filtering, time intelligence, or chronological sorting. A numeric amount stored as text may concatenate rather than calculate. A product identifier containing leading zeros should remain text, even if it contains only digits.

Locale

Locale controls how text is interpreted according to regional conventions. The value 03/04/2025 may mean 3 April in a day-month-year convention or March 4 in a month-day-year convention. A value such as 1.234,56 uses a common continental European convention, whereas 1,234.56 uses a common English-language convention.

When converting text to a date or number, use Data Type → Using Locale when the source format differs from the computer or development environment. The locale describes the source representation, not necessarily the report user's location. Record the choice in documentation because an incorrect locale can produce valid-looking but wrong values.

Dates and times also raise time-zone issues. A timestamp from a cloud service may be in Coordinated Universal Time while users expect local time. Converting a date-time value without identifying its time zone can shift records across reporting days.

6. Pivoting and Unpivoting

Pivot columns

Pivoting turns distinct values in one column into separate columns. Suppose a table contains Product, Month, and Sales. Pivoting Month creates columns such as January, February, and March, with Sales as the values. An aggregation function is required when more than one record exists for a Product-Month combination.

Pivoting is useful for presentation and some specialized calculations, but it creates a wider and often less flexible structure. New categories may create new columns during refresh, complicating a stable model.

Unpivot columns

Unpivoting converts columns into attribute-value rows. A table with Product, January, February, and March becomes Product, Month, and Sales. This is usually preferable for analytical models because measures remain in one column and categories remain in another.

A robust unpivot pattern is to select identifier columns and choose Unpivot Other Columns. This protects the query when new period columns are added. By contrast, selecting only January, February, and March may cause future columns to be ignored.

7. Grouping, Sorting, and Filtering

Group By

Group By summarises rows according to one or more grouping keys. For example, transactions can be grouped by Customer ID to calculate total sales, transaction count, or maximum transaction date. A grouped result is an aggregation table, not a replacement for the detailed transaction table unless detail is no longer required.

The aggregation must match the meaning of the field. Sum is suitable for additive monetary amounts when records are not duplicated. Count Rows counts records, while Count Values counts non-null values in a selected column. These are not interchangeable.

Sorting

Sort rows ascending or descending to support inspection, sequencing, or presentation. Sorting a text column is lexical rather than numeric; values such as 1, 10, and 2 may appear in the order 1, 10, 2 if they are text. Convert to the proper type before sorting.

Sorting does not create a permanent business ranking unless the ranking is explicitly calculated and preserved. Relational data has no inherent order, and later operations may remove or change the displayed order.

Filtering

Filters can be applied to text, numbers, dates, and errors. Use filters to remove test records, retain a reporting period, or isolate valid status values. Avoid filtering out data merely because it is inconvenient. A missing value may be an important data-quality finding.

Parameters are preferable to hard-coded dates or file paths when users or administrators need to change the reporting period or source location. They improve maintainability and support controlled deployment.

8. An Applied Case: Retail Sales Preparation

The following is an illustrative case.

Background

A regional retailer receives monthly sales spreadsheets from five branches. Each file contains a branch heading, transaction rows, and monthly columns named Jan, Feb, and Mar. Product codes are text, dates are written in day-month-year format, and branch names are sometimes abbreviated.

Problem

The reporting team wants a Power BI model that can compare sales by branch, product, and month. The original files contain repeated headings, inconsistent branch names, text-formatted amounts, and a wide monthly layout.

Analysis

The desired analytical structure is one row per branch-product-month. Therefore, the monthly columns should be unpivoted. Product codes must remain text because some begin with zero. The source locale is day-month-year, so dates must be interpreted accordingly.

Approach

First, combine the files from the folder and remove non-data heading rows. Promote the appropriate row to headers only after identifying the true header. Rename columns consistently and standardise branch values, for example changing N. Leeds to North Leeds.

Next, select the identifying columns Branch, Product Code, Product Name, and Transaction Date. Use Unpivot Other Columns to convert Jan, Feb, and Mar into Month and Sales Amount. Replace non-numeric markers with null only after confirming that they represent unavailable values. Set Product Code and Branch as text, Transaction Date as Date using the source locale, and Sales Amount as a suitable decimal type.

Finally, filter out rows that are genuinely outside the reporting scope, inspect errors, and validate totals against the original files. If duplicate transaction rows are present, investigate them rather than simply removing duplicates.

Outcome

The resulting long-format table can support a stable measure such as total sales by month and branch. New month columns can be incorporated more safely when the query uses Unpivot Other Columns, although the ingestion process should still be monitored for unexpected fields.

Learning points

The case demonstrates that transformation decisions depend on the intended model. Unpivoting improves analytical flexibility; locale settings protect date accuracy; preserving identifiers prevents loss of leading zeros; and validation against source totals provides evidence that the transformation has not changed the business meaning.

9. Quality, Governance, and Professional Practice

A transformation is successful only when it produces correct, understandable, and reproducible data. Recommended practices include:

• retain an untouched staging query when practical;
• use descriptive query and column names;
• add comments or documentation for non-obvious rules;
• apply data types explicitly;
• check row counts and key totals before and after major steps;
• inspect errors rather than hiding them;
• distinguish null, zero, blank, and not applicable;
• avoid unnecessary duplication of queries and data;
• use parameters for environment-specific settings;
• protect confidential data and minimise personally identifiable information;
• control access to source credentials and query outputs.

Power Query transformations can expose personal data to report developers or service environments. Data minimisation, access control, retention policies, and organisational privacy requirements should therefore be considered. A transformation that copies customer names into an unnecessary output table may increase privacy risk without improving analysis.

10. Common Failure Modes and Contemporary Practice

Common failures include automatic type detection based on a small sample, incorrect locale selection, filtering before understanding the data, filling nulls without a valid rule, and hard-coding a list of columns that changes over time. Another failure is using a merged text label as a key when the underlying components are not unique.

Modern Power BI development increasingly emphasises reusable dataflows, parameterised queries, metadata-driven ingestion, and data-quality monitoring. AI-assisted features may suggest transformations or detect patterns, but suggestions require human verification. An AI system may infer the wrong locale, classify a code as a number, or recommend replacing missing values. Accountability for the resulting dataset remains with the analyst and organisation.

Chapter Summary

Power Query transformations convert source-oriented data into a reliable analytical structure. Selecting and removing fields controls scope; renaming and reordering improve usability; splitting and merging change column structure; replacing and filling manage inconsistent or incomplete values; and explicit data types ensure that values are interpreted correctly. Locale settings are essential when dates and numbers follow regional conventions.

Pivoting creates columns from category values, whereas unpivoting creates attribute-value rows and is often more suitable for analytical models. Grouping produces summaries, sorting supports inspection, and filtering applies defined inclusion rules. Effective transformation design depends on step order, query folding where available, validation, documentation, privacy protection, and awareness of source changes. The central professional principle is to transform data without silently changing its meaning.

Key Terms

Data transformation: A controlled change to data structure, content, or representation.

Power Query: Microsoft's environment for connecting to and transforming data.

Applied step: A recorded Power Query operation executed in sequence.

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

Locale: Regional convention used to interpret dates, numbers, and text.

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

Null: An absent or unknown value.

Fill Down: Copying the previous non-null value into following null rows.

Fill Up: Copying the next non-null value into preceding null rows.

Pivot: Converting values in a category column into separate columns.

Unpivot: Converting multiple columns into attribute-value rows.

Group By: Aggregating rows according to one or more keys.

Aggregation: A summary operation such as sum, count, minimum, or maximum.

Delimiter: A character used to separate parts of a value.

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

Parameter: A controlled value used to make a query configurable.

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

Data minimisation: Limiting collected or processed data to what is necessary.

Review Questions

Short Answer Questions

1. Why should data types be applied explicitly in Power Query?

1. What is the difference between a null value and a zero?

1. What does Unpivot Other Columns accomplish?

1. Why can locale affect date and number conversions?

1. What is query folding?

Long Answer Questions

1. Explain how selecting, renaming, and reordering columns can improve the usability and maintainability of a Power BI query.

1. Compare pivoting and unpivoting, and explain why unpivoted data is often preferred for analytical models.

1. Discuss the risks of replacing or filling missing values without understanding their business meaning.

1. Describe a robust process for preparing a spreadsheet containing inconsistent headings, text-formatted numbers, and regional date formats.

1. Explain how step order, validation, and documentation contribute to reliable Power Query solutions.

Analytical / Application Questions

1. A sales file stores amounts as 1.234,50 and dates as 31/12/2025. Which locale and data types should be considered, and what risks arise from using an incompatible locale?

1. A table contains Customer, January, February, and March columns, but next year's file may add April. Should the query explicitly unpivot the three existing month columns or unpivot all other columns after selecting Customer? Justify your choice.

1. A department name appears only on the first row of each section, followed by several transaction rows. Explain when Fill Down is appropriate and identify one validation check that should be performed afterward.

Multiple Choice Questions with Answers

Question 1

Which transformation converts several month columns into an attribute column and a value column?

A. Pivot

B. Unpivot

C. Group By

D. Fill Down

Correct Answer: B. Unpivot

Question 2

A product code is 00125. Which data type should normally be used to preserve its leading zeros?

A. Whole Number

B. Decimal Number

C. Text

D. Date

Correct Answer: C. Text

Question 3

What does locale primarily control during conversion of text values?

A. The number of rows returned

B. Regional interpretation of dates and numbers

C. The order of Applied Steps

D. Whether columns are hidden in reports

Correct Answer: B. Regional interpretation of dates and numbers

Question 4

Which operation is most appropriate for summarising total sales by Customer ID?

A. Group By with Sum

B. Split Column by Delimiter

C. Fill Up

D. Replace Values

Correct Answer: A. Group By with Sum

Question 5

Why can replacing a missing sales amount with zero be problematic?

A. Zero cannot be stored in Power Query

B. A missing amount may mean unknown rather than no sale

C. Zero always changes a text value into a date

D. Power Query automatically deletes zero values

Correct Answer: B. A missing amount may mean unknown rather than no sale

Question 6

What is query folding?

A. Compressing a report page

B. Converting rows into columns

C. Translating compatible transformations to the source system

D. Replacing errors with null values

Correct Answer: C. Translating compatible transformations to the source system

Question 7

A column contains values 1, 10, and 2 as text. What is the likely result of sorting it as text?

A. 1, 2, 10

B. 10, 2, 1

C. 1, 10, 2

D. The values will always be treated as dates

Correct Answer: C. 1, 10, 2

Question 8

When is Fill Down most appropriate?

A. When every null represents a different unknown value

B. When a source layout displays a category once for a following group of rows

C. When converting currency into percentages

D. When removing duplicate records

Correct Answer: B. When a source layout displays a category once for a following group of rows

Question 9

Which approach is generally more robust when new month columns may be added?

A. Unpivot Other Columns after selecting identifier columns

B. Delete all future columns

C. Convert month names to row numbers manually

D. Sort the columns alphabetically and leave them wide

Correct Answer: A. Unpivot Other Columns after selecting identifier columns

Question 10

Which statement about sorting is correct?

A. Sorting creates a permanent relational order for all later operations

B. Sorting text always produces numeric order

C. Sorting can support inspection, but data should not be assumed to have inherent order

D. Sorting automatically removes null values

Correct Answer: C. Sorting can support inspection, but data should not be assumed to have inherent order