Text.ToList is a Power Query M function that converts a text value into a list of character values. The function returns a list containing the individual character values.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
Text.ToList( text as text ) as listDescription
Returns a list of character values from the given text value text. Simply put, it takes a text value and converts it into a list of separate characters. Each character in your text becomes an item in a list.
Examples
Let’s move from theory to practice and look at a few examples to see the Text.ToList function in action.
Basic Extraction
Consider the phrase “Hello World”. With the Text.ToList function, you can split this into a list of individual characters:
Text.ToList( "Hello World" )
This can be particularly useful when you’re analysing text data at a granular level, perhaps for a challenge that wants you to count the occurrences of a character, or allocating a value to each letter in the alphabet.
In another scenario you have a string, say “JohnDoe”, and you want to split it into individual letters. Here’s how you can do it:
Text.ToList( "JohnDoe" ) // Returns {"J", "o", "h", "n", "D", "o", "e"}You may use this when generating initials or processing text data for machine learning algorithms.
Handling Numeric Values
While Text.ToList is primarily designed for text values, there may be situations where one can think of using the function on a number. Let’s say you have a number value. And you want to compute the sum of its individual digits.
The Text.ToList function can be useful here. Since the function requires text as input, you would first transform the number into text.
Text.From( 55883 ) // Output: "55883"In this state, you can apply Text.ToList to return a list of numbers formatted as text.
Text.ToList( Text.From( 55883 ) ) // Output: { "5", "5", "8", "8", "3" }All of the list items are currently text values. Before we can sum them up, we need to transform them into number values. You can do that by using List.Transform and applying the Number.From function.
List.Transform(
Text.ToList( Text.From( 55883 ) ),
Number.From
) // Output: { 5, 5, 8, 8, 3 }You can then sum up the numbers by using List.Sum.
List.Sum(
List.Transform(
Text.ToList( Text.From( 55883 ) ),
Number.From
)
) // Output: 29Conclusion
Breaking down a number into individual digits can be essential for specific analytical tasks, such as calculating the sum of a number’s digits, performing digital root calculations, or other calculations. These techniques offer a method to perform such granular analyses on numeric data, extending the application of the Text.ToList function beyond text strings.
Related articles
Learn more about Text.ToList 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
Related functions
Other functions related to Text.ToList are:
