Text.ToList

Updated on

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 list

Description

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" )
Text.ToList function to split strings in Power Query M

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: 29

Conclusion

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.

Learn more about Text.ToList in the following articles:

Other functions related to Text.ToList are:

BI Gorilla Blog

Contribute » | Contributors: Rick de Groot, Mahmoud Bani Asadi
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/text-tolist