Text.ToList
Splits a text value into a list with one character in each item.
Text.ToList(text as nullable text) as listReturns: 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
- text: the value to split into characters. There are no other 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
- Each item is one character, so the list can be long for a long text. Do not use it for a whole column of memos.
- Text.Contains with a single character works for membership, but it is case sensitive for letters.
- An empty text gives an empty list.
- Null in gives null out.
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.