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.