Power Query Transforms

Reshaping data in Power Query

Setting the right data types

Data types are the foundation. If a number column is typed as Text, you can’t sum it. If a date column is typed as Text, time intelligence won’t work.

Data Type	Use For	Example
Whole Number	Counts, IDs, quantities	OrderQuantity: 42
Decimal Number	Money, percentages, measurements	Revenue: 1234.56
Text	Names, codes, categories	ProductName: “Organic Avocado”
Date	Dates without time	OrderDate: 2026-04-15
Date/Time	Timestamps with time component	CreatedAt: 2026-04-15 14:30:00
True/False	Boolean flags	IsActive: TRUE

How to change: Click the type icon in the column header, or select the column → Transform → Data Type.

💡 Exam tip: locale and data types

Date parsing depends on your locale setting. “04/06/2026” is April 6 in the US (MM/DD) but June 4 in most other countries (DD/MM). If dates look wrong after import, check File → Options → Regional Settings in Power Query, or use “Using Locale” when changing the data type to specify the correct format.

Creating and transforming columns

Riley at Coastal Fresh (🛒) needs to calculate profit per product. The source data has Revenue and Cost columns but no Profit column.

Custom Column vs Conditional Column

Method	When to Use	Example
Custom Column	Calculate a value using a formula	Profit = [Revenue] - [Cost]
Conditional Column	Assign values based on conditions (like IF)	If Revenue > 10000 then “High” else “Low”
Column from Examples	Power Query infers the pattern from your examples	Type “Q1” for Jan, “Q1” for Feb — PQ figures out the quarter logic

Custom Column uses M language syntax:

• Add Column → Custom Column
• Formula: [Revenue] - [Cost]

Conditional Column uses a visual IF builder:

• Add Column → Conditional Column
• If [Region] equals “North” then “NZ North” else if [Region] equals “South” then “NZ South”

Other common column transforms

Transform	What It Does	Where
Split Column	Break one column into multiple (by delimiter, character count, etc.)	Transform or Right-click
Merge Columns	Combine two+ columns into one	Transform → Merge Columns
Extract	Pull out part of text (first/last characters, between delimiters)	Transform → Extract
Replace Values	Swap specific values for others	Right-click → Replace Values

Group By: aggregating rows

Group By collapses many rows into summary rows. It’s the Power Query equivalent of a SQL GROUP BY.

Nadia at Prism Agency (📊) has a table with one row per ad impression — 2 million rows. She doesn’t need impression-level detail for her dashboard. She uses Group By to aggregate:

• Group by: Campaign, Platform
• Aggregations: Sum of Impressions, Sum of Clicks, Sum of Spend

Result: 2 million rows collapse to ~150 rows (one per campaign-platform combination).

How to: Select columns → Transform → Group By → choose Basic or Advanced → add aggregation columns.

Basic vs Advanced Group By

Basic: Group by one column, one aggregation. Advanced: Group by multiple columns, multiple aggregations (sum, count, average, min, max, etc.).

Always use Advanced when you need more than one summary column — it saves you from doing multiple Group By steps.

Pivot, Unpivot, and Transpose

These three operations reshape your data structure. They sound similar but do very different things.

Pivot makes tables wider, Unpivot makes them longer, Transpose rotates them

Operation	What It Does	When to Use It
Pivot	Turns row values into column headers (long → wide)	Monthly data in rows needs to become Jan, Feb, Mar columns
Unpivot	Turns column headers into row values (wide → long)	Months as separate columns need to become a single 'Month' column with values
Transpose	Flips rows and columns entirely (rotates the table 90°)	A table where headers are in the first column instead of the first row

Unpivot: the most common exam scenario

Kenji at Apex Manufacturing (🏭) receives a spreadsheet where production data is laid out wide:

Factory	Jan	Feb	Mar	Apr
Tokyo	1200	1350	1100	1400
Shanghai	800	950	870	1020

This is great for humans to read but terrible for Power BI. He needs a long format for time intelligence:

Factory	Month	Production
Tokyo	Jan	1200
Tokyo	Feb	1350
Shanghai	Jan	800

How to Unpivot:

1. Select the columns to keep fixed (Factory)
2. Select the month columns → Right-click → Unpivot Columns
3. Rename the generated “Attribute” and “Value” columns to “Month” and “Production”

💡 Exam tip: Unpivot Columns vs Unpivot Other Columns

• Unpivot Columns: Unpivots only the selected columns. If new months are added to the source, they won’t be included.
• Unpivot Other Columns: Unpivots everything EXCEPT the selected columns. New months added to the source ARE automatically included.

On the exam, Unpivot Other Columns is almost always the better answer because it handles future data gracefully.

Pivot: the reverse operation

Pivot turns long data into wide format. Use it when you need values from a column to become separate columns — for example, turning a “Metric” column with values “Revenue”, “Cost”, “Profit” into three separate columns.

Transpose: rotating the entire table

Transpose flips rows and columns entirely — rotating the table 90 degrees. Use it when your data arrives with headers in the first column instead of the first row.

Kenji receives a summary table where metric names are in column A and months are rows:

	Values
Revenue	1200
Cost	800
Profit	400

After Transpose (Transform → Transpose), each metric becomes a column header:

Revenue	Cost	Profit
1200	800	400

After transposing, you typically need to Use First Row as Headers (Transform → Use First Row as Headers) to promote the first data row into column names.

Knowledge check

Question

What's the difference between a Custom Column and a Conditional Column?

Click or press Enter to reveal answer

Answer

Custom Column uses M formula syntax for calculations (e.g., [Revenue] - [Cost]). Conditional Column uses visual IF/THEN logic to assign values based on conditions.

Click to flip back

Question

What does Unpivot do?

Click or press Enter to reveal answer

Answer

Turns column headers into row values — converting a wide table into a long table. Essential for time series data where months/years are separate columns.

Click to flip back

Question

Why is 'Unpivot Other Columns' usually better than 'Unpivot Columns'?

Click or press Enter to reveal answer

Answer

Unpivot Other Columns unpivots everything except the selected columns. If new columns are added to the source (e.g., new months), they're automatically included in the unpivot.

Click to flip back

Knowledge Check

Kenji receives factory production data with months as column headers (Jan, Feb, Mar...). He needs to create a time series chart. What Power Query operation should he use?

APivot ColumnsBUnpivot Other ColumnsCTransposeDGroup By

Check Answer

Knowledge Check

Riley notices that her 'OrderDate' column shows dates as '15/04/2026' but Power BI parsed them as '04/15/2026' (swapping day and month). What's the most likely cause?

AThe column data type is set to TextBThe regional locale setting doesn't match the source data formatCThe column needs to be unpivoted firstDPower Query cannot handle DD/MM/YYYY formats

Check Answer

Next up: Combining Data: Merge, Append and Queries — bring data from multiple tables together.