Splitter.SplitTextByWhitespace

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Splitter.SplitTextByWhitespace

What is Splitter.SplitTextByWhitespace?

The Splitter.SplitTextByWhitespace function is used to split a text string into a list of words. The function takes a single parameter, which is the text string to be split. The output of the function is a list of words, where each word is a separate element in the list.

The M Code Behind Splitter.SplitTextByWhitespace

The M code behind the Splitter.SplitTextByWhitespace function is relatively simple. The function is defined as follows:

Splitter.SplitTextByWhitespace = (text as text) as list => List.Split(Text.Trim(text), {" "}, Splitter.SplitTextOptions.TrimEmpty)

The function takes a single parameter, which is a text string. The text string is first trimmed using the Text.Trim function to remove any leading or trailing whitespace. The trimmed text string is then split using a delimiter of ” ” (a single space character). The Splitter.SplitTextOptions.TrimEmpty option is used to remove any empty elements from the resulting list.

Using Splitter.SplitTextByWhitespace

The Splitter.SplitTextByWhitespace function can be used in a variety of data manipulation scenarios. Here are a few examples of how the function can be used:

Example 1: Splitting a Name into First and Last

Suppose we have a table of names in the format “First Last”. We can use the Splitter.SplitTextByWhitespace function to split the names into separate columns for first and last name. Here is an example M code:

let

Source = Table.FromRows({{"John Smith"}, {"Jane Doe"}}, {"Name"}),

SplitNames = Table.AddColumn(Source, "First", each List.First(Splitter.SplitTextByWhitespace([Name]))),

SplitNames = Table.AddColumn(SplitNames, "Last", each List.Last(Splitter.SplitTextByWhitespace([Name])))

in

SplitNames

This code creates a table with a single column “Name”. The SplitNames step adds two new columns to the table: “First” and “Last”. The columns are populated using the Splitter.SplitTextByWhitespace function to split the “Name” column into first and last names.

Example 2: Counting Words in a Text Field

Suppose we have a table with a text field that contains sentences. We can use the Splitter.SplitTextByWhitespace function to count the number of words in each sentence. Here is an example M code:

let

Source = Table.FromRows({{"The quick brown fox"}, {"Jumped over the lazy dog."}}, {"Text"}),

WordCount = Table.AddColumn(Source, "Word Count", each List.Count(Splitter.SplitTextByWhitespace([Text])))

in

WordCount

This code creates a table with a single column “Text”. The WordCount step adds a new column to the table: “Word Count”. The column is populated using the Splitter.SplitTextByWhitespace function to count the number of words in each “Text” field.

The Splitter.SplitTextByWhitespace function is a powerful tool in the Power Query M language. By understanding the M code behind the function, you can use it to manipulate and transform your data in a variety of ways.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)