Text.SplitAny

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Text.SplitAny

Understanding Text.SplitAny

The Text.SplitAny function takes two arguments: the text string to split and a list of delimiters to use. The function splits the text string into an array of sub-strings based on any of the delimiters in the list. For example, the following code splits the text string “apple,banana,pear” into an array of three sub-strings:

let

textString = "apple,banana,pear",

delimiterList = {",", ";"}, // Delimiter list can be a single delimiter or a list of delimiters

result = Text.SplitAny(textString, delimiterList)

in

result

The resulting array contains three sub-strings: “apple”, “banana”, and “pear”.

The M Code Behind Text.SplitAny

The M code behind the Text.SplitAny function is relatively simple. The function takes the text string to split and the list of delimiters as arguments, then iterates over each character in the text string, checking to see if it matches any of the delimiters. If a delimiter is found, the current sub-string is added to the output array and the next sub-string is started.

Here is the M code for the Text.SplitAny function:

(Text as text, Delimiters as list) as list =>

let

SplitByDelimiters = (Text as text, Delimiters as list) as list =>

let

DelimiterCount = List.Count(Delimiters),

SplitText = List.Generate(

() => [StartIndex = 0, EndIndex = 0],

each [EndIndex] <> -1,

each [StartIndex = [EndIndex] + 1, EndIndex = Text.PositionOfAny(Delimiters, [StartIndex])],

each Text.Range(Text, [StartIndex], if [EndIndex] <> -1 then [EndIndex] - [StartIndex] else Text.Length - [StartIndex])

),

RemoveEmpty = List.RemoveItems(SplitText, {""})

in

RemoveEmpty,

Result = SplitByDelimiters(Text, Delimiters)

in

Result

The SplitByDelimiters function is where the actual splitting of the text string takes place. It uses the List.Generate function to iterate over each character in the text string, checking for the presence of any of the delimiters. The resulting sub-strings are then added to the output array.

Using Text.SplitAny in Power Query

Now that we understand the M code behind Text.SplitAny, let’s look at some examples of how it can be used in Power Query.

Splitting a Comma-Separated List

One common use case for Text.SplitAny is to split a comma-separated list into individual values. For example, let’s say we have a table with a column called “Fruits” that contains a comma-separated list of fruit names:

| Fruits |

|——–|

| apple,banana,pear |

| orange,grape |

| strawberry,kiwi,blueberry |

To split this column into individual fruit names, we can use the following code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIwtlEyNDBRyMxVyi9KLUpNzczJzSs1MzGzNKpLzSxJrEwqzYxNLEyMjQwNjBNTMPKzcxJLEwNzAwNzYwTQpJLMlNSyKzNLM0oKk1MLS1KLC9Y0LzSvRikKNja0MjQxts7LLSjOz8lPSdLzSvRikwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fruits = _t]),

#"Split Fruits" = Table.SplitColumn(Source, "Fruits", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Fruits.1", "Fruits.2", "Fruits.3"})

in

#"Split Fruits"

This code first creates a table from the original data, then uses the Table.SplitColumn function to split the “Fruits” column into three new columns based on the comma delimiter. The resulting table contains one row for each fruit name:

| Fruits.1 | Fruits.2 | Fruits.3 |

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

| apple | banana | pear |

| orange | grape | null |

| strawberry | kiwi | blueberry |

Splitting a Text String by Multiple Delimiters

Another use case for Text.SplitAny is to split a text string based on multiple delimiters. For example, let’s say we have a table with a column called “Address” that contains a full address:

| Address |

|———|

| 123 Main St, Apt 4 |

| 456 Oak Dr |

| 789 Maple Ave, Unit B |

To split this column into separate columns for street address, apartment number, and unit number, we can use the following code:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIwtlEyNDBRyMxVyi9KLUpNzczJzSs1MzGzNKpLzSxJrEwqzYxNLEyMjQwNjBNTMPKzcxJLEwNzAwNzYwTQpJLMlNSyKzNLM0oKk1MLS1KLC9Y0LzSvRikKNja0MjQxts7LLSjOz8lPSdLzSvRikwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t]),

#"Split Address" = Table.SplitColumn(Source, "Address", Splitter.SplitTextByDelimiter(Text.Combine({",", "Apt", "Unit"}, "|"), QuoteStyle.Csv), {"Street Address", "Apartment Number", "Unit Number"}),

#"Replace Separators" = Table.ReplaceValue(#"Split Address", "|", ", ", Replacer.ReplaceText,{"Street Address", "Apartment Number", "Unit Number"})

in

#"Replace Separators"

This code first creates a table from the original data, then uses the Table.SplitColumn function to split the “Address” column into three new columns based on the delimiters “,”, “Apt”, and “Unit”. The resulting table contains one row for each address component:

| Street Address | Apartment Number | Unit Number |

|—————-|—————–|————-|

| 123 Main St | Apt 4 | null |

| 456 Oak Dr | null | null |

| 789 Maple Ave | null | Unit B |

Note that the delimiters are combined into a single delimiter using the Text.Combine function, then separated again using the “|” separator. This is necessary because the SplitTextByDelimiter function only accepts a single delimiter, not a list.

The Text.SplitAny function in Power Query M is a powerful tool for splitting text strings into arrays of sub-strings based on one or more delimiters. By understanding the M code behind this function, you can use it to perform complex text manipulation tasks in Power Query.

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)