Text.Split - Text Function

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.

Splitting a String by a Delimiter

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.

Extracting Date Components

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" }

Splitting a List of Email Addresses

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", "," )

Learn more about Text.Split in the following articles:

Text Functions in Power Query M (150+ Examples)
Your guide to Text Functions in Power Query M. Learn from practical examples and master Power Query’s most useful Text functions. » Read more
Removing Excess Spaces Between Words in Power Query
This article will showcase three effective methods to removing spaces between words gracefully. We’ll delve into strategies from creating recursive functions with the ‘@’ operator to employing ‘List.Generate’ for iterative replacements. We’ll also explore a unique approach to splitting and combining text values. » Read more

Other functions related to Text.Split are:

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