Splitter.SplitByNothing

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will take a closer look at the M code behind the Splitter.SplitByNothing function and explore its various use cases.

Understanding the Splitter.SplitByNothing Function

The Splitter.SplitByNothing function is used to split a text string into multiple columns based on a delimiter. However, unlike other split functions, it does not require a delimiter to perform the split. Instead, it simply splits the text string at every character boundary.

The syntax for the Splitter.SplitByNothing function is as follows:

Splitter.SplitByNothing(text as nullable text, optional options as nullable record) as list

The text parameter is the text string that you want to split, while the options parameter is an optional record that contains additional options for the split operation.

Using the Splitter.SplitByNothing Function

The Splitter.SplitByNothing function can be used in a variety of scenarios. Here are a few examples:

Splitting Text into Columns

One of the most common use cases for the Splitter.SplitByNothing function is to split text into columns. To do this, you simply select the column that contains the text you want to split, and then choose the Split Column option from the Transform tab.

In the Split Column dialog box, you can choose the Splitter.SplitByNothing function as the split delimiter. Power Query will automatically split the text into multiple columns based on every character boundary.

Removing Extra Spaces

Another useful application of the Splitter.SplitByNothing function is to remove extra spaces from text. To do this, you can use the Text.Replace function to replace all instances of multiple spaces with a single space, and then use the Splitter.SplitByNothing function to split the text into individual words.

For example, consider the following text string:

" This is a test "

To remove the extra spaces and split the text into individual words, you can use the following M code:

let

Source = " This is a test ",

ReplaceSpaces = Text.Replace(Source, " ", " "),

SplitWords = Splitter.SplitByNothing(ReplaceSpaces)

in

SplitWords

The resulting output will be a list of individual words:

{"This", "is", "a", "test"}

Splitting Dates

The Splitter.SplitByNothing function can also be used to split dates into individual components. For example, if you have a date column that contains dates in the format “MM/DD/YYYY”, you can use the Splitter.SplitByNothing function to split the dates into individual components.

To do this, you can use the Text.Split function to split the date string into three separate strings, and then convert each string to a number using the Number.FromText function. Here is an example M code:

let

Source = Table.FromRecords({

[Date = "01/01/2020"],

[Date = "02/15/2020"],

[Date = "03/31/2020"]

}),

SplitDate = Table.TransformColumns(Source, {"Date", each Text.Split(_, "/")}),

ConvertToNumbers = Table.TransformColumns(SplitDate, {"Date", each List.Transform(_, Number.FromText)})

in

ConvertToNumbers

The resulting output will be a table with three columns: Month, Day, and Year.

The Splitter.SplitByNothing function is a powerful tool in the Power Query arsenal. Its ability to split text into columns based on every character boundary makes it a versatile function that can be used in many different scenarios. Whether you are splitting text into columns, removing extra spaces, or splitting dates into components, the Splitter.SplitByNothing function is sure to come in handy.

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)