🔁 Power Query Transform Ribbon in Power BI – A Complete Guide (with Image)

Author: TechTown.in

sample file:

techtown_sales_dataDownload

🧭 Introduction

When you’re working with raw data in Power BI Desktop, the Transform tab inside Power Query Editor becomes your ultimate toolkit. It provides all the tools needed to clean, shape, and prepare your data before using it in dashboards.

In this guide, we’ll explore all the Transform tab options using the exact screen from Power BI’s Power Query Editor, where the dataset is named techtown_sales_data_jan2025.

📊 Use case: This data includes fields like Date, OrderID, CustomerName, ProductCategory, and ProductName.

Here’s what the Power Query Editor Transform tab looks like:

🔧 Complete Breakdown of Transform Options

Let’s break down each option shown under the Transform ribbon in Power Query Editor.

🔷 Table Tools

1. Transpose

Flips the rows and columns of a table. Useful when your column headers are actually values.

2. Use First Row as Headers

Promotes the first row to become column headers (already applied in the screenshot via “Promoted Headers”).

3. Count Rows

Adds a row count to help you track total entries. Common in summarization steps.

4. Reverse Rows

Reverses the current order of rows (top to bottom becomes bottom to top).

🔶 Any Column (Flexible Tools for Any Data Type)

5. Rename

Quickly rename any selected column. You can also double-click the column header directly.

6. Detect Data Type

Automatically detects the most appropriate data type (e.g., date, number, text).
📌 Tip: You can manually override this with Data Type dropdown.

7. Replace Values

Replaces one value with another. Helpful for fixing typos or unifying categories (e.g., “mobile” vs “Mobile”).

8. Fill

  • Fill Down: Copies value from the row above into empty cells.
  • Fill Up: Does the opposite—copies the value from below.

9. Pivot Column

Turns rows into columns. Ideal for when you want to aggregate values (like sales by region).

10. Unpivot Columns

Flattens wide tables. Useful when you want to turn monthly columns (Jan, Feb, Mar) into one column.

🔷 Text Column Tools

11. Format

Applies formatting options:

  • UPPERCASE
  • lowercase
  • Capitalize Each Word
  • Trim (removes extra spaces)
  • Clean (removes non-printable characters)

📌 Example: Turn “sunita kapoor” → “Sunita Kapoor”

12. Merge Columns

Combines two or more columns into one.
E.g., merge CustomerID + CustomerName with a space in between.

13. Split Column

Breaks a column into multiple columns based on:

  • Delimiter (comma, space, etc.)
  • Number of characters
  • Uppercase/lowercase transitions

✅ Great for splitting full names or extracting product codes.

🔶 Extract (from Text Columns)

Allows extraction of:

  • First N characters
  • Last N characters
  • Range of characters
  • Text before/after a delimiter

🛠️ Example: Extract brand name from ProductName

🔢 Number Column Tools

14. Standard

Includes arithmetic operations like:

  • Add, Subtract, Multiply, Divide
  • Percent of
  • Absolute value
  • Rounding

15. Scientific

Scientific notation transformations like logarithms, exponentials.

16. Trigonometry

Sine, cosine, tangent—useful for geometrical data.

17. Rounding

Includes:

  • Round up/down
  • Round to nearest integer/decimal

18. Date & Time

Applies only to Date or DateTime fields (like Date in the screenshot):

  • Extract Year, Month, Quarter
  • Day of week
  • Time components (Hour, Minute)

19. Duration

Calculate durations (in days, hours, etc.) between two date-time fields.

20. Information

Detects column statistics (nulls, distinct values, min/max, etc.)

🔹 Scripts (Advanced)

21. Run R Script / Python Script

Allows running custom code to transform data beyond the GUI.
Great for:

  • Machine Learning preprocessing
  • Statistical analysis
  • NLP on text fields

🧠 Note: Requires R or Python installed on your system.

📋 Applied Steps (Right Sidebar)

In your screenshot, the following steps are already applied:

  • Source: The original Excel or CSV file
  • Promoted Headers: The first row became column headers
  • Changed Type: Automatically applied data types based on column values

These steps are:

  • Recorded automatically
  • Non-destructive
  • Reorderable or removable

📁 Dataset Columns (from Screenshot)

Let’s briefly look at the columns being transformed:

Column NameDescription
DateOrder date (converted to type: Date)
OrderIDUnique transaction ID
CustomerIDUnique customer ID
CustomerNameFull name of customer
ProductCategoryProduct type (e.g., Laptops)
ProductNameSpecific product name

🛠️ Sample Use Case from Screenshot

Let’s say we want to:

  1. Split ProductName column to extract brand and model
  2. Format CustomerName to capitalize each word
  3. Add a custom column to tag “High Value” orders (e.g., if product is a Laptop)

Power Query can handle all of this without a single line of code — just using the Transform ribbon!

🎓 Conclusion

The Transform tab in Power Query Editor is where your raw data becomes analysis-ready. Whether you’re:

  • Cleaning messy text
  • Fixing formats
  • Creating new columns
  • Pivoting rows

…it all happens right here.

With just a few clicks, you gain full control over your data—no need to be a coder or SQL expert!

📎 Related Links