Text.Length
Counts the characters in a text value.
Text.Length(text as nullable text) as nullable numberReturns: The number of characters, or null when the input is null.
What it does
Text.Length counts characters. In finance it is mostly a data quality check. An account code should be exactly six characters, a bank routing number nine, and a memo field should not exceed a set limit that the target system enforces. Counting the characters finds the rows that break the rule before they reach a report.
Syntax and arguments
- text: the value to count. Numbers must be converted with Text.From first.
Example
An ERP export is supposed to carry six digit account numbers. Rows with any other length are flagged so the team can fix them in the source system.
let
Accounts = #table(type table [Account = text, Name = text],
{{"100000", "Cash"}, {"21000", "Accounts payable"}, {"4010", "Product sales"}}),
Checked = Table.AddColumn(Accounts, "Length", each Text.Length([Account]), Int64.Type),
Flagged = Table.SelectRows(Checked, each [Length] <> 6)
in
Flagged
Two rows have the wrong length, "21000" and "4010". The Cash row has six characters and passes. The team can see which codes need attention without reading all of them.
Example: a second, different use
Count the characters in a vendor name to find names too long for a check printer's payee line, so they can be shortened before the run.
Things to watch
- Count characters after trimming. A padded value such as " 1000 " has a length of 6, not 4.
- A number converted to text can lose leading zeros, so count the text form you actually store.
- Length is about characters, not bytes. Accented letters count as one character each.
- Null in gives null out, so a check on Length may need to allow for nulls.
In business terms
Length checks are the cheapest control in a data load. They catch truncated account numbers, malformed bank references and oversized memos before a bad row reaches the close.