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

  1. Column has a problem
  2. Should the original column be kept? Yes: Use Add Column to derive a new one. No: Use a Transform tool in place.
  3. Pick the tool: type, split, replace, trim, group
  4. Check column quality for errors
  5. 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

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic