Split, Trim & Combine Text with Ease in Power Query M Language

Image by Editor
 

Power Query M Language provides powerful and flexible text manipulation functions that help you clean, transform, and process text data efficiently. In this tutorial, we will show you how to easily split, trim, and combine text using Power Query M Language.

You can load your table from Excel to power Query.

1. Text Trimming Functions

Power Query offers three primary trimming functions:

Text.Trim(): Removes whitespace from both the start and end of a string.

Syntax:

Text.Trim(text as nullable text, optional trimChars as any) as nullable text

Syntax:

• text: The string you want to trim.
• trimChars [Optional]: A list of characters to remove instead of spaces.

Text.TrimStart(): Removes whitespace from the beginning of a string.

Syntax:

Text.TrimStart(text as nullable text, optional trimChars as any) as nullable text

• text: The input string.
• trimChars [Optional]: A list of characters to remove instead of just spaces.

Text.TrimEnd(): Removes whitespace from the end of a string.

Text.TrimEnd(text as nullable text, optional trimChars as any) as nullable text

• text: The input string.
• trimChars [Optional]: Characters to remove from the end instead of spaces.

1.1. Basic Trimming

Removes all leading and trailing white spaces (or any specified characters) from a given text string.

Power Query M Code:

let
    // Original text with extra whitespace
    rawText = "   Hello, World!   ",
    
    // Trim all whitespace
    trimmedText = Text.Trim(rawText),           // Result: "Hello, World!"
    trimmedStart = Text.TrimStart(rawText),     // Result: "Hello, World!   "
    trimmedEnd = Text.TrimEnd(rawText)          // Result: "   Hello, World!"
in
    trimmedText

1.2. Use Custom Column to Trim

If you want to clean names directly inside the table (without converting to a list), you can use Table.AddColumn to apply trimming and formatting row by row.

Text.Trim([Raw Name])

1.3. Custom Character Trimming

You can also specify custom characters to trim using the trimChars parameter.

Power Query M Code:

let
    specialText = "###Remove Hashtags###",
    trimmedText = Text.Trim(specialText, "#")       // Result: "Remove Hashtags"
in
    trimmedText

2. Text Splitting Techniques

Divides a text string into a list of parts based on a specified delimiter.

Syntax:

Text.Split(text as text, separator as text) as list

• text: The full string to be split.
• separator: The delimiter (e.g., space, comma, hyphen) used to split the string.

2.1. Basic Splitting

Use Text.Split() to divide a string into an array based on a delimiter. Let’s split a co-joined text.

Power Query M Code:

let
    rawText = "John,Doe,35,Engineer",
    splitValues = Text.Split(rawText, ",")        // Result: {"John", "Doe", "35", "Engineer"}
in
    splitValues

2.2. Advanced Splitting with Regular Expressions

For more complex splitting, you can use Text.SplitAny() or regex-based splitting:

Power Query M Code:

let
    complexText = "Apple;Banana,Cherry|Date",
    splitResult = Text.SplitAny(complexText, ";,|")
in
    splitResult

3. Text Combining Methods

Combines a list of text values into a single string with an optional separator.

Syntax:

Text.Combine(list as list, optional separator as nullable text) as text

• list: A list of text values (e.g., {“First”, “Last”}).
• Separator [Optional]: String to place between each text (e.g., space, comma).

3.1. Simple Text Concatenation

Use the & operator or Text.Combine() for joining strings.

Power Query M Code:

let
    // Concatenation with & operator
    firstName = "John",
    lastName = "Doe",
    fullName = firstName & " " & lastName,        // Result: "John Doe"
    
    // Text.Combine() for array joining
    words = {"Power", "Query", "Magic"},
    combinedText = Text.Combine(words, " ")       // Result: "Power Query Magic"
in
    fullName

3.2. Conditional Text Combination

Combine text with conditions using if-then logic:

Power Query M Code:

let
    name = "Alice",
    age = 28,
    description = 
        if age >= 18 
        then name & " is an adult" 
        else name & " is a minor"
in
    description

Use Custom Column Formula

By using Power Query M language in a custom column formula, you can trim, split, and combine text.

• We started by trimming extra spaces from the Raw Name column using the Text.Trim function and created a new custom column called Trimmed Name.
• Next, we split the trimmed text by space using Text.Split, storing the result in a column named Split by Space.
• From this list, we extract the first item as FirstName using List.First and the last item as LastName using List.Last.
• Finally, we combine the LastName and FirstName in reverse order with a comma using Text.Combine, creating a new column called Full Name.

This structured step-by-step transformation makes the text clean, standardized, and ready for reporting or analysis.

let
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Raw Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Trimmed Name", each Text.Trim([Raw Name])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Split by Space", each Text.Split(Text.Trim([Raw Name]), " ")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "FirstName", each List.First(Text.Split(Text.Trim([Raw Name]), " "))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "LastName", each List.Last(Text.Split(Text.Trim([Raw Name]), " "))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Full Name", each Text.Combine({[LastName], [FirstName]}, ", "))
in
    #"Added Custom4"

Practical Example: Complete Name Cleaning Workflow

Here’s a comprehensive example demonstrating multiple text manipulation techniques:

Power Query M Code:

let
    rawTable = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
    nameList = rawTable[Raw Name],
    CleanNames = List.Transform(
        nameList,
        each Text.Proper(Text.Trim(_))
    ),
    ResultTable = Table.FromList(CleanNames, Splitter.SplitByNothing(), {"Cleaned Name"})
in
    ResultTable

Performance Tips

• Use Text.Trim() before splitting to ensure clean data
• Leverage List.Transform() for batch text operations
• Consider regular expressions for complex text parsing

Conclusion

Power Query M Language offers robust text manipulation capabilities. By mastering these techniques, you can efficiently clean, transform, and process text data in your Power Query workflows. These functions will help you to trim whitespace and custom characters easily, split text using various delimiters, and you can combine text with flexibility.