Text.Split

Updated on

Text.Split is a Power Query M function that splits a text value into a list of text values based on a specified delimiter. The function returns a list of text values resulting from the split operation.

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

Syntax

Text.Split(
   text as text,
   separator as text,
) as list

Description

The Text.Split function returns a list of text values resulting from the splitting of a text value text based on the specified delimiter, separator.

Here, ‘text’ refers to the string of text you wish to split. ‘Separator’ is the specific delimiter you want the function to use when splitting the text. The result is a list of text values that previously belonged together in the initial text string.

Examples

Let’s see how the Text.Split function works with some practical examples.

Imagine you have a string of text, “Name|Study|Nationality”, where each item is separated by a “|”. You want to split this text into a list of individual elements. Here’s how to use Text.Split for this task:

// Output: { "Name", "Study", "Nationality" }
Text.Split( "Name|Study|Nationality", "|" ) 
Text.Split to extract values from a string in Power Query M

This command would return a list of three text values: “Name”, “Address”, and “PhoneNumber”. Whereas functions like Table.SplitColumn create additional columns, Text.Split stores the split values as list within a single cell.

Let’s consider another example. Suppose you are given a date in the format “MM-DD-YYYY” and you want to extract each component separately. For example, you have the date “07-05-2023” and you want to extract the month, day, and year as individual elements. Text.Split can achieve this with ease:

Text.Split( "07-05-2023", "-" ) // Output: { "07", "05", "2023" }

For another example, think of a situation where you receive a list of comma-separated email addresses and you need to extract each one separately. With a string like “alex@example.com,sara@example.com,john@example.com”.

Text.Split is a good candidate to perform this task:

// Output: { "alex@example.com, "sara@example.com", "john@example.com" }
Text.Split( "alex@example.com,sara@example.com,john@example.com", "," )

In conclusion, Text.Split offers an easy way to split text in Power Query M.

Learn more about Text.Split in the following articles:

Other functions related to Text.Split are:

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