Splitter.SplitTextByAnyDelimiter

Splitter.SplitTextByAnyDelimiter is a Power Query M function that returns a function that splits text into a list of text at any of the specified delimiters. The function returns a list of text segments after splitting.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Splitter.SplitTextByAnyDelimiter(
   delimiters as list,
   optional quoteStyle as nullable number,
   optional startAtEnd as nullable logical,
) as function

Argument	Attribute	Description
Delimiters		A list of text values that act as delimiters when splitting text values.
Quotestyle	Optional	The QuoteStyle.Type determines how the function handles quotations in the text values. You can choose between QuoteStyle.None or QuoteStyle.Csv.
startAtEnd	Optional	A true/false value. When set to true, the function begins splitting the text from its end, rather than the beginning. This can lead to different results, for example when quotes are present in the text.

Description

The essence of the Splitter.SplitTextByAnyDelimiter function is its ability to segment a text string into multiple parts based on the provided delimiters.

You can optionally provide a QuoteStyle.Type and indicate to segment the values based on the provided delimiters starting at the end of the input by using providing a boolean value for the startAtEnd argument

Examples

Let’s have a look at a few practical examples for using Splitter.SplitTextByAnyDelimiter.

Basic Splitting

For instance, consider a text string with varying delimiters, such as commas, semicolons, or pipe symbols:

// Output: {"Jane", "Doe", "25", "Female", "NY" }
Splitter.SplitTextByAnyDelimiter( { ",", ";", "|" } )( "Jane;""Doe"", 25|Female;NY" )

Working with Quotes

The function offers flexibility with quotes with its support for a QuoteStyle.Type. You might want the function to respect or disregard them. Look at the difference when specifying QuoteStyle.Csv versus QuoteStyle.None:

// Output: {"Jane", "Doe", "25", "Female", "NY" }
Splitter.SplitTextByAnyDelimiter( { ",", ";", "|" }, QuoteStyle.Csv )( "Jane;""Doe"", 25|Female;NY" )

// Output: {"Jane", ""Doe"", "25", "Female", "NY" }
Splitter.SplitTextByAnyDelimiter( { ",", ";", "|" }, QuoteStyle.None )( "Jane;""Doe"", 25|Female;NY" )

Whereas QuoteStyle.Csv ignores the quotes, QuoteStyle.None returns the additional quotes in the output string.

Start Splitting at the End

The third argument of this function allows you to extract the elements from the end of the string. The output will still be from start till end, but the parts in which to split are analyzed starting at the end. With this in mind, to split the input by comma or semicolon, ignoring quotes and quoted delimiters and starting from the end of the input you can use the following code.

// Output: {"a,b", "c", "d"}
Splitter.SplitTextByAnyDelimiter( {",", ";"}, QuoteStyle.Csv, true )( "a,""b;c,d" )

The argument expects a true or false value. Now, a handy way to understand this better is:

// Output: {"a,b", "c", "d"}
let
    startAtEnd = true
in
    Splitter.SplitTextByAnyDelimiter( {",", ";"}, QuoteStyle.Csv, startAtEnd )( "a,""b;c,d" )

If you choose to reverse the order, by adjusting the startAtEnd argument to false, the result would be different.

// Output: {"a", "b,c;d"}
Splitter.SplitTextByAnyDelimiter( {",", ";"}, QuoteStyle.Csv, false )( "a,""b;c,d" )

Extracting domain from URL

Let’s say we have the url of this article: “https://powerquery.how/splitter-splittextbyanydelimiter/”. And we want to extract the domain name.

// Output: { "", "powerquery", "/splitter-splittextbyanydelimiter/" }
let
  SplitterFunction = SplitTextByAnyDelimiter( {"https://", ".how" } )
  ApplyFunction = SplitterFunction( "https://powerquery.how/splitter-splittextbyanydelimiter/" )
in
  ApplyFunction

The above code returns a list with 3 items and the domain is always the second item in the list. By using item-selection you can always return the second item in the list. Let’s see that in action with the following dataset.

Splitter.SplitTextByAnyDelimiter dataset for splitting

When your goal is to retrieve the different domain names, it’s important to first identify the delimiters that separate the domains. Besides “https://” and “.how”, below examples also have the delimiters “.bi”, “www.” and “.com”.

You can add those to the code as follows:

let
  SplitterFunction = SplitTextByAnyDelimiter({"https://","www.", ".how", ".bi", ".com" })
  ApplyFunction = SplitterFunction( [URL] )
in
  ApplyFunction

Splitter.SplitTextByAnyDelimiter returning a list of values

The splitting operation looks for different positions to split the text. When a delimiter is equal to the starting characters of a text, the output value contains an empty string (“”) as first list item. To get the domain name, we can add a step that retrieves the second list item.

let
  SplitterFunction = SplitTextByAnyDelimiter({"https://","www.", ".how", ".bi", ".com" })
  ApplyFunction = SplitterFunction( [URL] )
  RetrieveDomain = ApplyFunction{1}  // selects the second field in the list
in
  RetrieveDomain

Splitter.SplitTextByAnyDelimiter using multiple delimiters to retrieve domain

This formula, when placed in a custom column, will generate a new column with domain names.

To try it for yourself you can add below code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY1BDsIgEEXvwlqYvVdpWACO7SRTQJhm5PY2VWPd/fy8lzdNZhGp/QpQi2J7bNiGW4pCr0wi2OxnPCWOkMcNmVbafzD+8pPn0og5uEjA1MXet5yESu5vTFXdCTlS9mhBahiE8mwlRMYTH+lrpLL+Kcb7Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [URL = _t]),
    AddDomain = Table.AddColumn(Source, "Domain", each Splitter.SplitTextByAnyDelimiter({"https://","www.", ".how", ".bi", ".com" })([URL]){1})
in
    AddDomain

To conclude, the Splitter.SplitTextByAnyDelimiter is a flexible function makes text splitting an easy task. Especially when dealing with varying delimiters.

Used by

While you can use the Splitter.SplitTextByAnyDelimiter function by itself, it also works together with:

Other functions related to Splitter.SplitTextByAnyDelimiter are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/splitter-splittextbyanydelimiter