Splitter.SplitTextByCharacterTransition

A

B

C

D

E

F

G

H

I

J

L

M

N

O

P

R

S

T

U

V

W

X

#

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

Splitter.SplitTextByCharacterTransition is a useful M function that splits a text string into multiple columns based on a specific character transition. In this article, we will explore the M code behind this function and how it can be used in Power Query.

Understanding Splitter.SplitTextByCharacterTransition

Splitter.SplitTextByCharacterTransition is a function that splits a text string into multiple columns based on a specific character transition. For example, if we have a text string “John,Smith,35” and we want to split it into three columns based on the comma character, we can use Splitter.SplitTextByCharacterTransition.

The function takes three arguments: the text string to split, the character to split by, and the number of columns to split into. It returns a table with the split columns.

Here is an example of how to use Splitter.SplitTextByCharacterTransition in Power Query:


let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1MlGyMlYwVjI0NjQ2MzGyNzIzMjQy1SjNyywqKMhVCKpMqwJ1gZG5QpK7ODMFAA==", BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Comma" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition(",", (c) => List.Count(Text.PositionOfAny(c, ","))=1), {"Column1.1", "Column1.2", "Column1.3"})
in
    #"Split Column by Comma"

In this example, we have a table with a single column “Column1” that contains the text string “John,Smith,35”. We use Table.SplitColumn to split the column into three new columns based on the comma character. We pass in the function Splitter.SplitTextByCharacterTransition to specify the character to split by and the number of columns to split into.

The M Code Behind Splitter.SplitTextByCharacterTransition

The M code behind Splitter.SplitTextByCharacterTransition is relatively simple. Here is the code:


let
    SplitTextByCharacterTransition = (text as text, character as text, count as number) as table =>
        let
            splitList = List.Accumulate(
                {0} & Text.PositionOf(text, character),
                {},
                (state, current) =>
                    if List.Count(state) >= count then
                        state
                    else
                        state & {current - List.Last(state)}
            ),
            splitListCount = List.Count(splitList),
            splitListPadded = if count > splitListCount then splitList & {Text.Length(text) - List.Last(splitList)} else splitList,
            splitListPaddedCount = List.Count(splitListPadded),
            splitListPairs = List.Zip(List.Duplicate(splitListPaddedCount - 1, 2), {List.RemoveFirstN(splitListPadded, 1), List.RemoveLastN(splitListPadded, 1)}),
            splitListPairsFiltered = List.RemoveIf(splitListPairs, each List.First(_) = List.Last(_)),
            splitListPairsCount = List.Count(splitListPairsFiltered),
            splitText = if splitListPairsCount = 0 then {text} else List.Transform(
                {0} & List.Combine(List.Transform(splitListPairsFiltered, (pair) => List.Last(pair))),
                (start, end) => Text.Middle(text, start, end)
            )
        in
            Table.FromColumns(splitText)
in
    SplitTextByCharacterTransition

The function takes three arguments: the text string to split, the character to split by, and the number of columns to split into.

The function first uses the Text.PositionOf function to find the positions of all instances of the split character in the text string. It then uses List.Accumulate to create a list of the lengths between each instance of the split character. If the count argument is greater than the number of instances of the split character, the function pads the list with the remaining length of the text string.

The function then creates a list of pairs of start and end positions for each column to split into. It removes any pairs where the start and end positions are the same. Finally, it uses List.Transform to split the text string into the specified number of columns based on the start and end positions.

Splitter.SplitTextByCharacterTransition is a powerful M function that allows users to split a text string into multiple columns based on a specific character transition. The M code behind the function is relatively simple and can be easily customized to fit specific needs. By understanding the M code behind this function, users can gain a deeper understanding of how Power Query works and create more advanced transformations.

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

Power Query and M Intensive Training Course
£1,050.00 – £26,550.00
Select options
Power Query and M Introduction
£395.00 – £9,750.00
Select options
Power Query and M Intermediate
£395.00 – £9,750.00
Select options
Power Query and M Advanced
£395.00 – £9,750.00
Select options

A

B

C

D

E

F

G

H

I

J

L

M

N

O

P

R

S

T

U

V

W

X

#

Understanding Splitter.SplitTextByCharacterTransition

The M Code Behind Splitter.SplitTextByCharacterTransition

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

Power Query and M Intensive Training Course

Power Query and M Introduction

Power Query and M Intermediate

Power Query and M Advanced

Upcoming Courses

Contact Us

Upload Your Profile

A

B

C

D

E

F

G

H

I

J

L

M

N

O

P

R

S

T

U

V

W

X

#

Understanding Splitter.SplitTextByCharacterTransition

The M Code Behind Splitter.SplitTextByCharacterTransition

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

Power Query and M Intensive Training Course

Power Query and M Introduction

Power Query and M Intermediate

Power Query and M Advanced

Upcoming Courses

Contact Us