Power of Text Functions in Power BI

 Text functions in Power BI provide powerful tools to manipulate, format, and analyze textual data. These functions are invaluable when working with datasets containing names, addresses, or any textual information. In this blog, we will explore the most commonly used text functions in Power BI, their syntax, and practical examples.

Key Text Functions in Power BI

1. CONCATENATE and CONCATENATEX

• CONCATENATE: Joins two text strings into one.

Syntax:

CONCATENATE(<text1>, <text2>)

Example: Combine first and last names:

Full Name = CONCATENATE(Employees[FirstName], Employees[LastName])

• CONCATENATEX: Joins text strings across rows of a table using a delimiter.

Syntax:

CONCATENATEX(<table>, <expression>, [delimiter])

Example: List all product names in a category separated by commas:

Product List = CONCATENATEX(Products, Products[ProductName], ", ")

---

2. LEFT and RIGHT

• LEFT: Extracts a specified number of characters from the start of a text string.

Syntax:

LEFT(<text>, <num_chars>)

Example: Extract the first three letters of a product code:

Product Prefix = LEFT(Products[ProductCode], 3)

• RIGHT: Extracts a specified number of characters from the end of a text string.

Syntax:

RIGHT(<text>, <num_chars>)

Example: Extract the last four digits of a phone number:

Last Four Digits = RIGHT(Customers[PhoneNumber], 4)

---

3. MID

Extracts a substring starting from a specific position and length.

Syntax:

MID(<text>, <start_num>, <num_chars>)

Example: Extract the middle portion of a product code:

Middle Code = MID(Products[ProductCode], 2, 3)

---

4. LEN

Calculates the number of characters in a text string.

Syntax:

LEN(<text>)

Example: Find the length of a customer name:

Name Length = LEN(Customers[Name])

---

5. TRIM

Removes all leading and trailing spaces from a text string.

Syntax:

TRIM(<text>)

Example: Clean up extra spaces in a customer name:

Clean Name = TRIM(Customers[Name])

---

6. REPLACE

Replaces part of a text string with another text string.

Syntax:

REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)

Example: Replace the first three characters of a product code:

New Product Code = REPLACE(Products[ProductCode], 1, 3, "NEW")

---

7. SEARCH

Finds the starting position of a substring within a text string.

Syntax:

SEARCH(<find_text>, <within_text>, [start_num], [not_found_value])

Example: Find the position of a hyphen in a product code:

Hyphen Position = SEARCH("-", Products[ProductCode])

---

8. UPPER and LOWER

• UPPER: Converts a text string to uppercase.

Syntax:

UPPER(<text>)

Example: Convert a customer name to uppercase:

Upper Name = UPPER(Customers[Name])

• LOWER: Converts a text string to lowercase.

Syntax:

LOWER(<text>)

Example: Convert a customer name to lowercase:

Lower Name = LOWER(Customers[Name])

---

9. SUBSTITUTE

Substitutes occurrences of a substring with a new text string.

Syntax:

SUBSTITUTE(<text>, <old_text>, <new_text>, [instance_num])

Example: Replace all hyphens with slashes in a product code:

Reformatted Code = SUBSTITUTE(Products[ProductCode], "-", "/")

---

Why Text Functions Matter

Text functions allow you to:

• Standardize data formats (e.g., uppercase/lowercase conversion).
• Extract meaningful insights from unstructured textual data.
• Create dynamic labels or concatenated strings for reports.

With these functions, you can clean, manipulate, and transform textual data to enhance the quality of your Power BI reports.