Format Operation in Power Query Editor in Power BI with example

The Format Operation in Power Query Editor in Power BI is used to transform text data in a column to a specific format, such as changing the case, trimming spaces, or adding prefixes or suffixes. This operation is particularly useful for cleaning and standardizing text data before using it in reports.

---

Key Format Operations in Power Query:

1. Lowercase: Converts all text to lowercase.
2. Uppercase: Converts all text to uppercase.
3. Capitalize Each Word: Capitalizes the first letter of each word.
4. Trim: Removes all leading and trailing spaces from text.
5. Clean: Removes non-printable characters.
6. Add Prefix: Adds specified text at the beginning of each value.
7. Add Suffix: Adds specified text at the end of each value.

---

Example Table: Employee Details

EmployeeID	Name	Department	Salary
1	alice johnson	Sales	50000
2	BOB SMITH	IT	60000
3	CHARLIE BROWN	HR	55000
4	Diana Prince	Marketing	65000

---

Steps to Perform Format Operations:

1. Load the Dataset:

• Import the above dataset into Power BI.
• Open Power Query Editor by clicking Transform Data.

2. Apply Format Operations:

a. Capitalize Each Word in the Name Column:

• Select the Name column.
• Navigate to the Transform tab and click Format > Capitalize Each Word.
• Result:
  | Name |
  |——————–|
  | Alice Johnson |
  | Bob Smith |
  | Charlie Brown |
  | Diana Prince |

b. Trim Spaces in the Name Column:

• Select the Name column.
• Click Transform > Format > Trim.
• This removes any extra spaces before or after the names.
• Result:
  | Name |
  |——————–|
  | Alice Johnson |
  | Bob Smith |
  | Charlie Brown |
  | Diana Prince |

c. Add Prefix to the Department Column:

• Select the Department column.
• Click Transform > Format > Add Prefix.
• In the dialog box, type Dept: as the prefix.
• Result:
  | Department |
  |——————–|
  | Dept: Sales |
  | Dept: IT |
  | Dept: HR |
  | Dept: Marketing |

d. Add Suffix to the Salary Column:

• Select the Salary column.
• Click Transform > Format > Add Suffix.
• In the dialog box, type USD as the suffix.
• Result:
  | Salary |
  |——————–|
  | 50000 USD |
  | 60000 USD |
  | 55000 USD |
  | 65000 USD |

---

Apply Changes:

• Once you’ve performed all necessary transformations, click Close & Apply to save the changes.

---

Teaching Tips:

1. Real-Life Use Case: Explain scenarios where data might be messy, such as inconsistent capitalization in names or unnecessary spaces in text columns.
2. Interactive Demo: Encourage students to explore each operation (e.g., try different prefixes or capitalize department names).
3. Error Handling: Show how to revert changes if the wrong format is applied.

By mastering the Format Operation, students will better understand how to clean and standardize data in Power Query Editor.