Downloads
Power Query MTextIntermediate

Text.ToList

Splits a text value into a list with one character in each item.

Syntax
Text.ToList(text as nullable text) as list

Returns: A list of single character text values.

What it does

Text.ToList turns a value into a list of its characters, one item for each character. It is useful when a rule must be applied to each character separately, such as a check digit calculation, or when the text is counted for a pattern. For ordinary splitting on a separator, Text.Split is the better tool.

Syntax and arguments

Example

A bank routing number is nine digits. A data check confirms that every character is a digit before the number is used in a payment file.

let
    Routing = "021000021",
    Characters = Text.ToList(Routing),
    AllDigits = List.AllTrue(List.Transform(Characters, each Text.Contains("0123456789", _))),
    Count = List.Count(Characters)
in
    {AllDigits, Count}

AllDigits is true and Count is 9. Each character is checked against the string of digits. A value with a letter in it would return false.

Example: a second, different use

Get the first character of each item in a list of codes to build a set of account type prefixes.

Things to watch

In business terms

Check digits, routing numbers and tax identifiers have rules that apply to one character at a time. Splitting the text into characters is how a data team checks those rules before a payment or a filing.

Related functions

All Power Query M functions · View as Markdown · M basics