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 listDescription
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", "|" ) 
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.
Related articles
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 tackle this issue 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
Related functions
Other functions related to Text.Split are:
