Splitter.SplitTextByLengths

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Splitter.SplitTextByLengths function

The Splitter.SplitTextByLengths function is used to split text into multiple columns based on the lengths of the substrings. It takes two arguments – the first argument is the text to be split, and the second argument is a list of integers that represent the lengths of the substrings. The function then splits the text into columns based on the lengths specified in the second argument.

For example, consider the following text: “John Doe 123 Main St New York NY 10001”. Suppose we want to split this text into four columns – one for the first name, one for the last name, one for the address, and one for the city, state, and zip code. We can use the Splitter.SplitTextByLengths function as follows:

Splitter.SplitTextByLengths("John Doe 123 Main St New York NY 10001", {4, 3, 10, 18})

This will split the text into four columns with the following values:

| Column 1 | Column 2 | Column 3 | Column 4 |

|———-|———-|—————-|————————|

| John | Doe | 123 Main St | New York NY 10001 |

The M code behind the Splitter.SplitTextByLengths function

The M code behind the Splitter.SplitTextByLengths function is relatively simple. It consists of a function that takes two arguments – the text to be split and the list of lengths. The function then uses the List.Zip function to combine the text with the lengths and create a list of tuples. Each tuple contains a substring of the text and its corresponding length.

The list of tuples is then passed to the List.Accumulate function, which iterates over each tuple and splits the text into columns based on the lengths specified in the tuple. The result is a list of lists, where each inner list represents a column of the split text.

Here is the M code for the Splitter.SplitTextByLengths function:

let

SplitTextByLengths = (text as text, lengths as list) =>

let

tuples = List.Zip({Text.ToList(text), lengths}),

result = List.Accumulate(tuples, {[ ]}, (state, current) =>

let

substring = List.FirstN(state{[List.Count(state)]}, current{[1]}),

rest = List.Skip(state{[List.Count(state)]}, current{[1]}),

newColumn = List.Combine({rest, {substring}})

in

state & {[newColumn]})

in

result

in

SplitTextByLengths

The Splitter.SplitTextByLengths function is a powerful tool for splitting text into multiple columns in Power Query. Its M code is relatively simple and can be easily customized to suit specific data transformation needs. By understanding the M code behind the function, data analysts can gain a deeper understanding of how Power Query works and how it can be used to transform data.

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)