Splitter.SplitTextByWhitespace

Splitter.SplitTextByWhitespace is a Power Query M function that splits text into a list at whitespace. The function returns a list of text segments after applying the whitespace-based splitting.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Splitter.SplitTextByWhitespace( optional quoteStyle as nullable number ) as function

Description

This Power Query M function splits a text string into separate parts wherever it finds whitespace characters, such as spaces, tabs, or line breaks. It’s especially useful when you need to clean up text that has irregular spacing.

QuoteStyle (optional argument) defines how the function handles text within quotes. You can choose between:

QuoteStyle.None: Quotes are treated like regular characters.
QuoteStyle.Csv: Text enclosed in quotes is treated as a single unit, preserving internal whitespace.

Examples

Here are some simple examples to help you understand how to use Splitter.SplitTextByWhitespace.

Example 1: Removing Extra Spaces from a Sentence

Imagine you have a sentence with lots of extra spaces, and you want to clean it up, keeping only one space between words.

Let’s take this sentence:

"This    sentence     has   too  many    spaces"

First, you can split the sentence in the Description column into individual words using Splitter.SplitTextByWhitespace:

// Output: { "This", "sentence", "has", "too", "many", "spaces" }
Splitter.SplitTextByWhitespace()( [Description] )

This transforms your sentence into a list. You can combine the values from the list back into a sentence by using the Text.Combine function on the Split column as follows:

// Output: "This sentence has too many spaces"
Text.Combine( [Split], " " )

This works great for any sentence that does not have any leading or trailing spaces. But trying this on the following sentence will not work:

"  Leading and    trailing   spaces   too  "

The final step therefore uses Text.Trim on the Combine column to also remove trailing and leading spaces.

Text.Trim( [Combine] )

Here’s an image showing each of the columns:

Splitter.SplitTextByWhiteSpace example that removes double spaces in Power Query M

Note that when there are consecutive spaces, this function does return any spaces in the resulting list. It removes all consecutive spaces. You can try this yourself using:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsnILFYAguLUvJLUvORUEFshIxEkVpKfr6CQm5hXCZYvSExOLVaK1YlWUlDwSU1MycxLV0jMSwHJlRQlZuaA+DBlMM1KsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Description = _t]),
    SplitText = Table.AddColumn(Source, "Split", each Splitter.SplitTextByWhitespace()([Description]) ),
    CombineText = Table.AddColumn(SplitText, "Combine", each Text.Combine( [Split], " " )),
    TrimText = Table.AddColumn(CombineText, "Trim", each Text.Trim( [Combine] ))
in
    TrimText

Example 2: Working with Quoting Characters

Sometimes you might have special characters or quotes in your text. You can control how they’re handled by using the optional parameter (quoteStyle).

If you want quotes and special characters treated as regular text, here’s what you do:

// Output: {"a", "b", "c"}
Splitter.SplitTextByWhitespace( QuoteStyle.None )( "a b#( tab )c" )

Now your sentence will split correctly, even if there are special characters.

Used by

While you can use the Splitter.SplitTextByWhitespace function by itself, it also works together with:

Other functions related to Splitter.SplitTextByWhitespace are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/splitter-splittextbywhitespace