Mastering Power BI Text Functions: The Complete Guide (Module 6.1)

In the ever-evolving world of business intelligence, transforming raw text into structured, insightful information is key to driving clarity and making informed decisions. Power BI offers a suite of powerful text functions that allow data professionals to clean, format, analyze, and manipulate text-based data efficiently. These functions become indispensable when working with inconsistent data sources, messy labels, or human-entered data that doesn’t follow a predictable pattern.

Whether it’s extracting a domain from an email, formatting names into a standardized structure, or converting numeric strings into usable numbers, Power BI’s text functions help streamline the ETL (Extract, Transform, Load) process. Module 6.1 of this Power BI series dives deep into the 21 essential DAX text functions and their Power Query equivalents. Each function is explained with practical examples for seamless integration into real-world dashboards and models.

DAX & Power Query Text Functions with Explanation

1. COMBINEVALUES

Purpose: Joins two or more text values with a delimiter.

DAX: COMBINEVALUES(" - ", [First Name], [Last Name])
Power Query: Text.Combine({[First Name], [Last Name]}, " - ")

Use: Create a Full Name column.

2. CONCATENATE

Purpose: Combines exactly two text values.

DAX: CONCATENATE([City], [Pincode])
Power Query: [City] & Text.From([Pincode])

3. CONCATENATEX

Purpose: Concatenates values across a table or group.

DAX: CONCATENATEX(VALUES(Subject), Subject, ", ")
Power Query: Text.Combine(List.Transform([Subjects], each Text.From(_)), ", ")

4. EXACT

Purpose: Case-sensitive comparison.

let
  name = [First Name],
  firstLetter = Text.Start(name, 1),
  rest = Text.Middle(name, 1),
  isProper = firstLetter = Text.Upper(firstLetter) and rest = Text.Lower(rest)
in
  isProper

5. FIND

Purpose: Find position of substring (case-sensitive).

DAX: FIND("@", [Email], 1)
Power Query: Text.PositionOf([Email], "@")

Will Check wheter email has @ or not

 if Text.PositionOf([Email], "@") >= 0 then "Correct" else "Incorrect" 

6. FIXED

Purpose: Format number as text with fixed decimals.

DAX: FIXED([Maths], 1, TRUE)
Power Query: Number.ToText([Maths], "0.0")

7. FORMAT

Purpose: Format number or date as string.

DAX: FORMAT([Maths], "0.00")
Power Query: Number.ToText([Maths], "0.00")

8. LEFT

Purpose: Extract characters from the start of text.

DAX: LEFT([Email], 5)
Power Query: Text.Start([Email], 5)

9. LEN

Purpose: Count number of characters.

DAX: LEN([Last Name])
Power Query: Text.Length([Last Name])

10. LOWER

Purpose: Convert text to lowercase.

DAX: LOWER([City])
Power Query: Text.Lower([City])

11. MID

Purpose: Extract characters from middle of string.

DAX: MID([Email], 1, 5)
Power Query: Text.Middle([Email], 0, 5)

12. REPLACE

Purpose: Replace part of string by position.

DAX: REPLACE([First Name], 1, 2, "XX")
Power Query: "XX" & Text.Range([First Name], 2, Text.Length([First Name])-2)

Step-by-Step Explanation

Assume:
[First Name] = "Rohan"

🔹 Text.Length([First Name])

Returns number of characters in the name.

"Rohan" → 5

🔹 Text.Range([First Name], 2, Text.Length([First Name]) - 2)

This means:

• Start from index 2 (3rd character, because indexing starts at 0)
• Take length – 2 characters from that point

📘 Breakdown:

Text.Range("Rohan", 2, 3) → starting from 3rd character → "han"

🔹 Final Expression:

"XX" & "han" → "XXhan"

13. REPT

Purpose: Repeat a text string.

DAX: REPT("*", [Maths]/10)
Power Query: Text.Repeat("*", Number.RoundDown([Maths]/10))

14. RIGHT

Purpose: Extract characters from end of string.

DAX: RIGHT([Email], 10)
Power Query: Text.End([Email], 10)

15. SEARCH

Purpose: Find position of substring (case-insensitive).

DAX: SEARCH("@", [Email], 1)
Power Query: Text.PositionOf([Email], "@")

Find whether Rohan is present or not

if Text.PositionOf([Name], "Rohan") >= 0 then "Found" else "Not Found"

16. SUBSTITUTE

Purpose: Replace all instances of substring.

DAX: SUBSTITUTE([Email], ".com", ".edu")
Power Query: Text.Replace([Email], ".com", ".edu")

17. TRIM

Purpose: Remove extra spaces.

DAX: TRIM([Location])
Power Query: Text.Trim([Location])

18. UNICHAR

Purpose: Return Unicode character from number.

DAX: UNICHAR(9786)
Power Query: Character.FromNumber(9786)

19. UNICODE

Purpose: Return number from Unicode character.

DAX: UNICODE(LEFT([First Name], 1))
Power Query: Character.ToNumber(Text.Start([First Name], 1))

20. UPPER

Purpose: Convert text to uppercase.

DAX: UPPER([First Name])
Power Query: Text.Upper([First Name])

21. VALUE

Purpose: Convert text to numeric.

DAX: VALUE("85")
Power Query: Number.FromText("85")

Summary

Understanding and applying Power BI’s text functions can greatly enhance your ability to manage and analyze textual data. Whether you’re preparing data in Power Query or building calculated columns using DAX, each of these 21 text functions plays a crucial role in shaping readable, structured, and valuable outputs.

These tools not only save time but also enforce consistency across your reporting models. By mastering these formulas, you’ll significantly improve data quality, readability, and user experience in your dashboards.

Stay tuned for Module 6.2: Conditional Text Logic and String-Based Measures!