Transform
Power BI · Power Query (Data Transformation)
Theory
The Transform tab changes existing columns in place. Common tools are Data Type (set text, whole number, decimal, date), Replace Values, Format (Trim, Clean, UPPERCASE), Split Column (by delimiter or number of characters), Group By, Fill Down and Pivot or Unpivot. Each tool adds one applied step.
Data types matter most: a column of numbers stored as text cannot be summed, and a value that cannot convert shows as Error, which you can find with column quality and fix with Replace Errors. Split Column is a typical clean-up: a source Name column holding "Asha Sharma" can be split by the first space into First Name "Asha" and Last Name "Sharma". Group By summarises rows, for example total Amount per Region, when you need a pre-aggregated table.
Why we use it
Source data arrives with mixed types, combined fields and inconsistent text. Transform tools fix these problems so relationships match, sums work and slicers show clean values.
When to use it
Use Transform when the column itself is wrong or needs reshaping. Use Add Column instead when you want to keep the original and create a new derived column. Set data types deliberately near the end; automatic type detection can guess wrongly.
How it works
- Column has a problem
- Should the original column be kept? Yes: Use Add Column to derive a new one. No: Use a Transform tool in place.
- Pick the tool: type, split, replace, trim, group
- Check column quality for errors
- Clean column ready to load
Syntax
Transform → Data Type: Whole Number | Decimal Number | Text | Date
Home/Transform → Split Column → By Delimiter (space, Left-most delimiter)
Transform → Replace Values | Format → Trim | Group By
M:
Table.SplitColumn(table, "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"First Name", "Last Name"})Example
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Name",
Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),
{"First Name", "Last Name"}),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",
{{"First Name", Text.Trim, type text}, {"Last Name", Text.Trim, type text}})
// Group By example
Table.Group(Sales, {"Region"}, {{"Total Amount", each List.Sum([Amount]), type number}})Key points
- Split Column by delimiter or number of characters
- Change Type sets the correct data type per column
- Conversion failures show as Error; use Replace Errors or fix the source
- Trim and Clean remove stray spaces and hidden characters
- Group By aggregates rows into a summarised table